Leandro, thanks for responding. I'm glad I didn't miss any of the
possibilities. I chose to keep a table for ProductViews because this
allows me to graph the information over time: what was hot this week,
what was hot last month, etc.
Part of what makes this difficult is that I really want a HOT product
list. And hot products are determined by a formula:
2*number of views on the home page +
5*number of views of the product page +
10*number of times the product was ordered
This makes the query pretty hairy. I decided to implement it as a
class method called hot_products. This way the caller can limit the
actual number himself.
Of course, I *could* have stashed this information in the Product
record, but then I'd be unable to report on the trends.
On 29 Dec, 2005, at 7:35 pm, Leandro Lucarella wrote:
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
--
Jeff Watkins
http://newburyportion.com/
Democracy n: A country where the newspapers are pro-American.
