SQL – ordering table by information from multiple tables

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

[2] post_score

post_id	score_id
1	1
1	2
1	3
2	4
2	5
2	6

and [3] post

id	title
1	title1
2	title2

My goal is to order [3] post table by score.

Assume UPVOTE represents value of 1 and DOWNVOTE value of -1; In this example, post where id = 1 has 3 scores related to it, and the values of them are UPVOTE, UPVOTE, DOWNVOTE, making the “numeric score” of this post: 2;

likewise, post where id = 2, also has 3 scores, and those values are: UPVOTE, DOWNVOTE, DOWNVOTE, making the “numeric score”: -1;

How would I order post table by this score? In this example, if I ordered by score asc, I would expect the following result:

id	title
2	title2
1	title1

My attempts didn’t go far, I am stuck here with this query currently, which doesn’t really do anything useful yet:

WITH fullScoreInformation AS (
    SELECT * FROM score s
    JOIN post_score ps ON s.id = ps.score_id),
    upvotes AS (SELECT * FROM fullScoreInformation WHERE rating_type = 'UPVOTE'),
    downvotes AS (SELECT * FROM fullScoreInformation WHERE rating_type = 'DOWNVOTE')
SELECT p.id, rating_type, title FROM post p JOIN fullScoreInformation fsi on p.id = fsi.post_id

I am using PostgreSQL. Queries will be used in my Spring Boot application (I normally use native queries).

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

Answer

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.