Whats the best way to implement time based sorting in php/mysql?

Question

I have 1 table filled with articles. For the purpose of this post, lets just say it has 4 fields. story_id, story_title, story_numyes, story_numno

Each article can be voted YES or NO. I store every rating in another table, which contains 3 fields: vote_storyid, vote_date (as a timestamp), vote_code (1 = yes, 0 = no).

So when somebody votes yes on an article, it run an update query to story_numyes+1 as well as an insert query to log the story id, date and vote_code in the 2nd table.

I would like to sort articles based on how many YES or NO votes it has. For "Best of all time" rating is obviously simple.... ORDER BY story_numyes DESC.

But how would I go about doing best/worst articles today, this week, this month?

I get the timestamps to mark the cut-off dates for each period via the following:

$yesterday= strtotime("yesterday");
$last_week = strtotime("last week");
$last_month = strtotime("last month");

But Im not sure how to utilize these timestamps in a mysql query to achieve the desired results.

Answer 1

Try something like

SELECT id,
SUM(CASE WHEN votedate >= $yesterday THEN 1 ELSE 0 END) AS daycount,
SUM(CASE WHEN votedate >= $last_week THEN 1 ELSE 0 END) AS weekcount,
SUM(1) AS monthcount
FROM votes
WHERE yes_no = 'YES'
AND votedate >= $last_month
GROUP BY id

Then make that a subquery and you can get the max values for the counts.

(Please allowing for the usual syntax sloppiness inherent in an untested query.)

---

In response to the comments:

To use it as an efficient subquery (i.e. not correlated) to get the maximum values:

SELECT
MAX(daycount) AS MaxDayCount,
MAX(weekcount) AS MaxWeekCount,
MAX(monthcount) AS MaxMonthCount
FROM
(
.... all that stuff ...
) AS qcounts

but of course you can't attribute them to ids, because they are different. If you want them one at a time with ids, you might

SELECT id, monthcount
FROM
(
.... all that stuff ...
) AS qcounts
ORDER BY monthcount DESC
LIMIT 1

and do it three times, once for day/week/month.

Note: this is all to illustrate some things you could accomplish in a single reasonably efficient query. I wouldn't be surprised if you were to find it's simplest (and simple == good) to break it up as others suggest.

Answer 2

In general:

select story_id, sum(vote_code)
from story_vote
group by story_id;

For particular vote date ranges:

select story_id, sum(vote_code)
from story_vote
where vote_date >= 'least date, inclusive' 
and vote_date < 'last date, exclusive'
group by story_id;

OP comments:

How would I use the ORDER BY clause?

You'd add an order by sum(vote_code). Descending if you want stories with the most votes first:

order by sum(vote_code) desc;

On edit: I notice he wants all stories, not one, so I'm removing the having clause.

Answer 3

SELECT a.*, SUM(vote_code) AS votes
FROM articles a JOIN votes v ON (a.story_id = v.vote_storyid)
WHERE v.vote_date >= $yesterday
GROUP BY a.story_id
ORDER BY 2 DESC;

Likewise for $last_week and $last_month.

If you want the results to be sorted, it's better to do this in separate queries, instead of trying to do it in a single query. Because the sort order may be very different for each of the three periods.

Answer 4

I came up with my own solution, but I ran into a new problem.

SELECT id, COUNT(id) AS total_votes
 FROM stories
 LEFT JOIN vote_log
 ON (vote_storyid = id)
 LEFT JOIN comments 
 ON (c_itemid = id)
 WHERE vote_date > '0' AND vote_code = 1 
 GROUP BY id
 ORDER BY total_votes desc

Since I join it to the comments table on the story id, it acts as a multiplier. Any way to avoid that?

All other solutions here suffer from the same problem.