CODEWITHSUNDEEP
sqlpostgresql
codesw1tch
codesw1tch

Reputation: 720

Postgresql order by sum of counts after joins

I have Posts, Comments, and Likes in my webapp. I would like to order posts by a 'score' which is simply the sum of likes and comments.

I'm trying to do something like this:

SELECT posts.id, COUNT(comments.id) AS c, COUNT(likes.id) AS l 
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 SUM(c,l) desc;

but I get an error about column c not existing;

Or this:

SELECT posts.id, sum(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;

but I get an error about name and argument types.

Is there a different way to write this query such that it works? Thanks!

Upvotes: 1

Views: 1278

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324861

You were on roughly the right track, but for some reason you tried to use the sum aggregate intead of simple addition:

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 2 desc;

I used the ordinal-position sort order here, i.e. order by 2nd column.

Upvotes: 5

Related Questions