Counting likes and comments between two different table of a specific topic

Question

I have two tables, likes and comments both of which refer to topic posts - like a topic of a forum for example.

Both of them have a column that refers to a specific topic_id.

Now here's the deal: i want to create a top 5 chart of most liked + commented, the total of both summed up i mean, topics.

For example i did this query for selecting from the topics table the most liked, i want to make the same chart with the total of likes + comments. Here's my top 5 topics by total of likes only.

SELECT topics.* ,
COUNT(q_id)
AS post_count 
FROM topics 
LEFT JOIN likes 
ON topics.id = likes.q_id 
WHERE topics.to_user = 'someuser' 
GROUP BY likes.q_id  
ORDER BY post_count DESC 
LIMIT 0, 5

Tnx in advance!

Answer 1

SELECT posts.id, count(comments.id) + count(likes.id) AS score 
FROM posts
LEFT JOIN comments ON posts.id = comments.post_id 
LEFT JOIN likes ON posts.id = likes.post_id 
GROUP BY posts.id 
ORDER BY score desc;

For those interested here's the solution.