View today / this week / this month / all time

[wesleyb]

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.

[SabreCEO]

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