I have a Spring MVC blog with functionality for Post and Comment voting. I want to return the top 3 users based on number of votes they've received on all their posts and comments.
tables:
users u [id, username]
posts p [id, u.id]
comments c [id, p.id, u.id]
post_votes pv [p.id, u.id, type (1 or -1)]
comment_votes cv [c.id, u.id, type (1 or -1)]
The following statement gives me total votes per user by querying two separate voting tables and then adding the totals together:
SELECT
(SELECT SUM(type)
FROM posts_votes pv
JOIN posts p ON p.id = pv.post_id
JOIN users u ON u.id = p.user_id
WHERE u.id LIKE ?1)
+
(SELECT SUM(type)
FROM comments_votes cv
JOIN comments c ON c.id = cv.comment_id
JOIN users u ON u.id = c.user_id
WHERE u.id LIKE ?1)
That works fine with a WHERE clause per user id... But now I'm trying to find just the top 3 users that have the most votes and I'm having too much difficulty. This is what I have so far:
SELECT u.id, u.username, IFNULL(SUM(pv.type), 0) AS totalPostVotes
FROM posts_votes pv
JOIN posts p ON p.id = pv.post_id
JOIN users u ON u.id = p.user_id
GROUP BY u.id ORDER BY totalPostVotes DESC LIMIT 3
That above statement works by itself giving me: u.id, u.username, and totalPostVote in descending order. So does the one below for comments:
SELECT u.id, u.username, IFNULL(SUM(cv.type), 0) AS totalCommentVotes
FROM comment_votes cv
JOIN comments c ON c.id = cv.comment_id
JOIN users u ON u.id = c.user_id
GROUP BY u.id ORDER BY totalCommentVotes DESC LIMIT 3
Great! But I want that third column SUM result to be essentially "totalVotes" and contain the sum of both of those subqueries. Then I'll GROUP BY u.id ORDER BY totalVotes DESC LIMIT 3.
Something like this:
SELECT u.id, u.username, SUM(
(SELECT IFNULL(SUM(pv.type), 0) AS totalPostVotes
FROM posts_votes pv
JOIN posts p ON p.id = pv.post_id
JOIN users u ON u.id = p.user_id
GROUP BY u.id ORDER BY totalPostVotes DESC LIMIT 1)
+
(SELECT IFNULL(SUM(cv.type), 0) AS totalCommentVotes
FROM comments_votes cv
JOIN comments c ON c.id = cv.comment_id
JOIN users u ON u.id = c.user_id
GROUP BY u.id ORDER BY totalCommentVotes DESC LIMIT 1))
AS totalVotes from users u
GROUP BY u.id, u.username ORDER BY totalVotes DESC LIMIT 3
id | username | totalVotes
2 user2 11
1 user1 11
29 user29 11
What's happening is the result of totalVotes is indeed the correct vote count, 11, for the "top" user, but none of those users are the real top user, and the correct vote is being repeated 3 times in the guise of other users. I'm not even sure how users are being sorted at that point because they're not in an order I recognize.
The subqueries work separately (they give me the correct user) when I add SELECT "u.id, u.username " IFNULL(SUM()) but then if I run the whole block, I get the error "Operand should contain 1 column(s)" So I delete them and revert to only SELECT IFNULL(SUM())
I'm also noticing the subqueries are only allowed LIMIT 1. How would I get the top 3, then? Should I do a UNION somewhere or is "+" sufficient? This is rather confusing. Can someone please help me with this? Any help is appreciated. Thanks in advance!
Updated code, thank you Peter:
SELECT
u.username,
pv_sum.total AS postTotal,
cv_sum.total AS commentTotal,
IFNULL(pv_sum.total, 0) + IFNULL(cv_sum.total, 0) as totalVotes
FROM users u
LEFT JOIN (
SELECT p.user_id, IFNULL(SUM(pv.type), 0) AS total
FROM posts p
JOIN posts_votes pv ON pv.post_id = p.id
GROUP BY p.user_id
) pv_sum ON pv_sum.user_id = u.id
LEFT JOIN (
SELECT c.user_id, IFNULL(SUM(cv.type), 0) AS total
FROM comments c
JOIN comments_votes cv ON cv.comment_id = c.id
GROUP BY c.user_id
) cv_sum ON cv_sum.user_id = u.id
GROUP BY u.username, postTotal, commentTotal
ORDER BY totalVotes DESC LIMIT 3;
