How can I sort by vote "by date", "by year" ..?

Question

What I'm trying to implement is similar to what we have on SO. I want to rank messages using upvotes on the last day, last month, etc. My schema consists of two tables,

post(id, post, posted_on..) vote(post_id, vote_value, date) 

I hope the scheme is pretty clear. The problem is that if I sort by day, creating an internal join by message and vote and having a where clause (" votes.date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY "), it works as intended but doesn’t show other messages. I mean that posts that did not have a vote on the last day are completely ignored. I want these messages to be given low priority but displayed in the request.

Although I might think about using the join operation, I was looking for a different approach.

Update : let's say there are two posts, 1,2.

and the vote table is like

 post_id vote_value date 1 1 2012-12-19 2 1 2012-12-10 

If I ask, according to my approach, then only the message “1” will appear, since I set a date limit, but I want both of them to be displayed. Here is my request:

 SELECT `id`, SUM(`votes`.`votes`) AS likes_t, `post`.* FROM `posts` JOIN `votes` ON (`id` = `votes`.`post_id`) WHERE `votes`.`date` >= DATE_SUB(CURDATE(), INTERVAL 2 DAY) 

Answer 1

If you want to show all messages, but only count the latest votes, this should do this:

 SELECT `id`, SUM(IF(`votes`.`date` >= DATE_SUB(CURDATE(), INTERVAL 2 DAY, `votes`.`votes`, 0)) AS likes_t, `post`.* FROM `posts` JOIN `votes` ON (`id` = `votes`.`post_id`) 

Answer 2

If I understood correctly:

SELECT *, IF(vote.date>=DATE_SUB(CURDATE(), INTERVAL 1 DAY), 1, 0) as rate FROM post INNER JOIN vote ON (post.id=vote.post_id) ORDER BY rate DESC;

 +------+--------+---------+------+---------------------+------+ | id | post | post_id | vote | date | rate | +------+--------+---------+------+---------------------+------+ | 1 | first | 1 | 1 | 2012-12-19 00:00:00 | 1 | | 1 | first | 1 | 1 | 2012-12-13 00:00:00 | 0 | | 2 | second | 2 | 1 | 2012-12-10 00:00:00 | 0 | +------+--------+---------+------+---------------------+------+