I have 2 tables, a ratings table and a comments table. I'm trying to select the number of likes and dislikes for each comment. Each comment can have multiple ratings (like or dislike). Currently, the rating table has a rating column which contains either 0 or 1. 0 is a dislike and 1 is a like.
My question is, how would I be able to count all the occurrences of likes and dislikes for each comment?
I've made this query that is able to return likes, I'd be able to return dislikes by adding another left join lateral and checking if it equals 0, however that would impact the queries performance and I don't think thats the best way of doing things.
SELECT comments.username, comments.text, comments.reply_to_id, l.likes from comments
LEFT JOIN LATERAL (
SELECT COUNT(*) AS likes FROM ratings AS likes WHERE likes.comment_id = replies.id AND likes.rating = 1
) as l on true WHERE comments.id = 1;
Is there a better way to do this?