Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

View today / this week / this month / all time

wesleybwesleyb EntrantInactive User Role Beginner
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.

Comments

  • SabreCEOSabreCEO Entrant Current User Role Beginner
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.