CODEWITHSUNDEEP
mysql
KvinT
KvinT

Reputation: 37

Sort comments by likes and dislikes

Help, please, correct the query, I need to sort comments by likes and dislikes (that is, by the sum of likes and dislays: cnt_total = cnt_like + cnt_dislike, who has more cnt_total is displayed above), and if cnt_total is equal - sort by date. cnt_total is just an example, it's not in the table.

Table comments with columns: id_comment, id_user, id_news, comment, id_parent, date_time, cnt_like, cnt_dislike, is_active.

SELECT u.login, c.* 
FROM comments c
LEFT JOIN users u ON u.id = c.id_user
WHERE id_news = '{$id_news}' AND c.is_active = 1 
ORDER BY id_parent, date_time DESC

Upvotes: 0

Views: 109

Answers (1)

Nick
Nick

Reputation: 147206

Try this:

SELECT u.login, c.*, c.cnt_like + c.cnt_dislike AS cnt_total 
FROM comments c
LEFT JOIN users u ON u.id = c.id_user
WHERE id_news = '{$id_news}' AND c.is_active = 1 
ORDER BY cnt_total DESC, date_time DESC

Upvotes: 1

Related Questions