MySQL how to get user rank by votes amount

Question

I have two tables. One "user" and one "votes", each user can have n votes. Now I select a list of users and output it. It works well with votes as COUNT in a LEFT JOIN, but now I want to calculate the RANK (1st, 2nd, 3rth, ...) of the user.

Theoretically very easy with ORDER BY Votes, but I also allow my users to SORT BY user date (when they joined). And I also have a pagination with a LIMIT in my query. So how do I solve this as best?

$user_sql   = $db->query("SELECT u.*, COUNT(v.voteId) AS user_votes 
                          FROM cs_users AS u
                          LEFT JOIN cs_users_votes AS v 
                          ON u.userId = v.voteUserId");

(Basic query, but with pagination and sorter ORDER BY / LIMIT etc. will be added)

Hope you can help!

i am confused about " but I also allow my users to SORT BY user date (when they joined)."??? — habib ul haq, Feb 26 '15 at 07:48
I mean, when I SORT BY user Date I can't use the order of the MySQL Output to get the rank, like counting $i++; every user, to get rank. Same problem when using LIMIT — Maxi, Feb 26 '15 at 07:50
did you try this SELECT u.*, COUNT(v.voteId) AS user_votes FROM cs_users AS u LEFT JOIN cs_users_votes AS v ON u.userId = v.voteUserId order by votes desc, date desc; — habib ul haq, Feb 26 '15 at 07:51
@Maxi can you tell me what you get the result of your query?? — habib ul haq, Feb 26 '15 at 08:10
try this SELECT u.*, COUNT(v.voteId) AS user_votes FROM cs_users AS u LEFT JOIN cs_users_votes AS v ON u.userId = v.voteUserId group by v.user_id order by count(v.voteUserid) desc limit 3 — habib ul haq, Feb 26 '15 at 08:28
Use one of the solutions from this: http://stackoverflow.com/questions/1895110/row-number-in-mysql, and use the query as a subquery to get desired sorting. — Salman A, Feb 26 '15 at 09:26
I think you should explain more about how you handle pagination. — Strawberry, Feb 26 '15 at 09:29

score 0 · Answer 1 · answered Feb 26 '15 at 09:23

0

Well, to count rank you can use the ranking function found here It will rank every user, taking in consideration result ties. If you add pagination you might try to pass the result from the previous page recursively.

As for the function itself

SET @curRank :=0, @prevRank := NULL;
SELECT u.*, COUNT(v.voteId) AS user_votes,
CASE 
WHEN @prevRank = id THEN @curRank 
WHEN @prevRank := id THEN @curRank := @curRank + 1
END AS rank
FROM cs_users AS u
LEFT JOIN cs_users_votes AS v 
ON u.userId = v.voteUserId

answered Feb 26 '15 at 09:23

Artjoman

286
1
9

Good, but I think you've forgotten the ORDER BY and LIMIT part. What impact will the OP's pagination method have on this query? – Strawberry Feb 26 '15 at 09:28
Yes. I just wanted to show the idea of ranking itself, not to give an exact solution. About pagination - there are several solutions - personally I would make a TEMP table with all the RANKS, using the query I have given, and then adding pagination to it. Table itself can be rebuild e.g. hourly or based on events (e.g. new vote). This will dramatically increase query speed as well, as there won't be a necessity to make any JOINs, just a query from a plain table – Artjoman Feb 26 '15 at 09:36

MySQL how to get user rank by votes amount

1 Answers1