Jeff Watkins, el jueves 29 de diciembre a las 22:13 me escribiste:
>
> As part of my data model, I'd like to know what's "hot". Therefore, I'm
> recording views of particular model items (products). In order to use
> this information, I'd like to perform a query like the following:
>
> select product_id, count(product_id) as temp from product_views order
> by temp group by product_id limit 5
>
> This should retrieve the top 5 most viewed products.
>
> The problem is, I don't know how to specify a query like this in
> SQLObject. Ideally, I'd prefer the count to simply appear as a property
> of the product and order by the number of views, but I suspect that's
> asking too much of SQLObject.
If you are concerned about performance, I think you should add an extra
fiedl to Product to count the views (this way you win a more natural way
to do that query in SQLObject, just select() the products using the
viewCount field to order).
If you would like to do the query, you can do something like:
1) Do the query in plain old SQL and get the top 5 products using
products = [Product.get(id) for id in query_result]
I would suggest doing this in a Product class method called getTop5 or
something, so you can just do products = Product.getTop5() in your
code and have all database-specific code in SQLObject classes
2) Just think in objects (I agree it's not very comfortable here):
prods = {}
for pv in ProductViews.select():
prods[pv.productId] = prods.get(pv.productId, 0) + 1
top5 = []
for id, freq in prods.iterItems():
top5.append((freq,id))
top5.sort(lambda x,y: -cmp(x,y))
top5 = top5[:5]
products = [Product.get(id) for freq, id in top5]
But I think the best option is to add a field to Products to store the
number of views it has (for performance and clearness).
This way all you have to do is:
1) Product.views += 1 # on each view
2) top5 = Product.select(orderBy=Product.q.views)[:5]
top5 = top5.reversed()
# for getting the top 5 viewed products
Just my 2 cents...
--
Leandro Lucarella (luca) | Blog colectivo: http://www.mazziblog.com.ar/blog/
.------------------------------------------------------------------------,
\ GPG: 5F5A8D05 // F8CD F9A7 BF00 5431 4145 104C 949E BFB6 5F5A 8D05 /
'--------------------------------------------------------------------'
<muBarder> Y vos, Nw2o? A vos no te conozco pero debes ser flor de bala!
<Nw2o> malcojido moral
