| View today / this week / this month / all time [message #1899] |
Wed, 19 September 2007 05:01  |
 wesleyb
Messages: 1
Registered: September 2007 |
Junior Member |
|
|
These different ways of viewing content can be seen more and more on "web 2.0" sites.
An example here:
http://www.shadowness.com/explore/topfavorites
You can show just the items favorited today, this week, this month, or all time.
So what is the best SQL to do this?
I assume I need a separate table for the favorites:
ITEM_FAVORITED
-------------
item_id
user_id
when
Here I will have to do a join + a count of the nr of rows where when < X (today, this week,..)
or
ITEM_FAVORITED
-------------
item_id
today
this_week
this_month
all_time
Problem with this scenario is that everything needs to be recalculated every day.
---
in both cases I would probably have to update the items table as well for easy access:
ITEMS
-----
id
all_time_favorited
Is there any better way? Anyone already implemented this?
Thank you.
|
|
|
| Re: View today / this week / this month / all time [message #1911 is a reply to message #1899 ] |
Wed, 19 September 2007 19:24  |
SabreCEO
Messages: 4
Registered: September 2007 |
Junior Member |
|
|
ITEM_FAVORITED
-------------
item_id
user_id
when
should work good. You'd probably want a key on item_id
The other option wouldn't work because say you have:
item_id 50
today 35
this_week 250
this_month 1500
all_time 8000
When the next day comes along, you'd want to add today to this_week, and subtract the results from the 8th day back. However, you can't know how many that is. It could be anywhere from 0-250, there is no way to know which counts are from what days. So you'd have to add a when field anyway, or a count for every single day...
-Mark
|
|
|
Members
Search
Help
Register
Login
Home
