These different ways of viewing content can be seen more and more on “web 2.0” sites.
An example here:
[URL]http://www.shadowness.com/explore/topfavorites[/URL]
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.