postgresql - SQL - ordering table by information from multiple tables

Question

Title of the question may not have been very clear - I am not really sure how to name this question, but I hope that my explanation will make my problem clearer.

I have 3 tables:

[1] score

id	rating_type
1	UPVOTE
2	UPVOTE
3	DOWNVOTE
4	UPVOTE
5	DOWNVOTE
6	DOWNVOTE

Answer 1

My goal is to order post table by score. Assume UPVOTE represents value of 1 and DOWNVOTE value of -1

One option uses a subquery to count the upvotes and downvotes of each post:

select p.*, s.*
from post p
cross join lateral (
    select 
        count(*) filter(where s.rating_type = 'UPVOTE'  ) as cnt_up,
        count(*) filter(where s.rating_type = 'DOWNVOTE') as cnt_down
    from post_score ps
    inner join score s on s.id = ps.score_id
    where ps.post_id = p.id
) s
order by s.cnt_up - s.cnt_down desc

Perhaps this data structure is bad and I should have constructed my entities differently ?

As it stands, I don't see the need for two distinct tables post_score and score. For the data you have showed, this is a 1-1 relationship, so just one table should be sufficient, storing the post id and the rating type.