frosty February 2016

Sort comments by upvotes-downvotes, make sure ones without votes are displayed above negative

I'm fetching comments and sorting them by their score (upvotes-downvotes). However if a comment has not been voted on at all, then it has NULL upvotes & downvotes, and so ones that are negative will be displayed first.

Upvotes are cv_type = 1, downvotes are cv_type = 0.

SELECT c_id,c_text,c_date,c_parentid,u_username,
(select sum(IF( cv.cv_type=  '1', 1 ,  0 )) from comment_votes cv where cv_commentid=comments.c_id ) as upvotes,
(select sum(IF( cv.cv_type=  '0', 1 ,  0 )) from comment_votes cv where cv_commentid=comments.c_id ) as downvotes
FROM comments
INNER JOIN users
ON u_id = c_userid
WHERE c_postid = ? AND c_parentid = 0
ORDER BY upvotes-downvotes
DESC LIMIT 100

Any way to make sure ones without votes are above ones that are negative?

Answers


Gordon Linoff February 2016

Just use the coalesce() function to replace the NULL values with 0:

SELECT c_id, c_text, c_date, c_parentid, u_username,
       coalesce((select sum(cv.cv_type = '1') from comment_votes cv where cv.cv_commentid= c.c_id ), 0) as upvotes,
       coalesce((select sum(cv.cv_type = '0') from comment_votes cv where cv.cv_commentid = c.c_id ), 0) as downvotes
FROM comments c INNER JOIN
     users u
     ON u.u_id = c.c_userid
WHERE c.c_postid = ? AND c.c_parentid = 0
ORDER BY upvotes - downvotes DESC
LIMIT 100;

Note: you should use table aliases and qualified column names instead of naming columns in a table with a prefix. Instead of using c_parentid, use c.parentid. Sometimes prefixes on column names are useful, but SQL uses table aliases to identify the table where a column comes from.

Post Status

Asked in February 2016
Viewed 2,053 times
Voted 4
Answered 1 times

Search




Leave an answer