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

[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 ?

GMB · Accepted Answer · 2020-12-31T13:13:57.557

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.

I may not understand something since your query did not work as is - I had to remove x.* at the top and change `where ps.post_id = p` to `where ps.post_id = p.id` - I'd get errors otherwise. With these changes, however, this works perfectly — Kleronomas, Dec 31 '20 at 12:57

score 1 · Answer 2 · answered Dec 31 '20 at 12:30

1

You better use a LEFT join, otherwise you wouldn't get posts that have no votes yet. Then aggregate to get the fitered sum of the scores. Then add these sums, apply coalesce() to get 0 for posts without votes and order by the result.

SELECT p.id,
       p.title
       FROM post p
            LEFT JOIN post_score ps
                      ON ps.post_id = p.id
            LEFT JOIN score s
                      ON s.id = ps.score_id
       GROUP BY p.id,
                p.title
       ORDER BY coalesce(sum(1) FILTER (WHERE rating_type = 'UPVOTE')
                         +
                         sum(-1) FILTER (WHERE rating_type = 'DOWNVOTE'),
                         0);

I second GMB's comment about the superfluous table.

answered Dec 31 '20 at 12:30

sticky bit

36,626
12
31
42

the reason for superfluous table is because I also use same system for comments. They can have score same way post can have. My current tables: score, post_score, comment_score. Does this change anything or does the table remain superfluous? – Kleronomas Dec 31 '20 at 12:36
1

The `score` table is superfluous, yes (unless there's more to it you didn't show). Instead of the `score_id` column in `post_score` you could just have a column that directly stores whether it was an up or down vote (or even just 1 or -1 directly). For the comments and their score that's analog. – sticky bit Dec 31 '20 at 12:43

SQL - ordering table by information from multiple tables

2 Answers2