mysql query help in selecting top votes

Question

i have table posts and posts_votes in posts_votes i have id and post_id and vote now i want select from posts where have top votes count in last 1 day using this to set the time

from_unixtime(post_date) >= SUBDATE(NOW(),1)

thanks in advance

Answer 1

maybe something like this:

SELECT
    posts.*,
    pv.votes
FROM
    posts JOIN
    (
        SELECT post_id, COUNT(*) AS votes FROM posts_votes GROUP BY post_id
    ) AS pv ON posts.id=pv.post_id
WHERE
    posts.post_date >= UNIX_TIMESTAMP()-86400
ORDER BY
    pv.votes DESC;

Answer 2

Utilizing the GROUP BY clause allows you to aggregate data on a particular column without resorting to a sub query or derived table.

This query returns all posts, ordered by vote count in descending order:

SELECT p.*
FROM posts p
JOIN posts_votes pv
ON pv.post_id = p.id
WHERE FROM_UNIXTIME(p.post_date) >= SUBDATE(NOW(), 1)
GROUP BY p.id
ORDER BY COUNT(pv.post_id) DESC

If you wanted to limit it to the top ten posts, you could add the LIMIT clause:

SELECT p.*
FROM posts p
JOIN posts_votes pv
ON pv.post_id = p.id
WHERE FROM_UNIXTIME(p.post_date) >= SUBDATE(NOW(), 1)
GROUP BY p.id
ORDER BY COUNT(pv.post_id) DESC
LIMIT 10

MySQL may be able to optimize the date comparison, however, in general, try to perform all of your functions on the constant (the right side) instead of on the column (the left side).

For example, you should change this:

WHERE FROM_UNIXTIME(p.post_date) >= SUBDATE(NOW(), 1)

To this:

WHERE p.post_date >= UNIX_TIMESTAMP(SUBDATE(NOW(), 1))

This way, MySQL doesn't have to run FROM_UNIXTIME() on every row. Again, MySQL may realize this and auto optimize, but it's best practice.