How to order the selection by the number of related entities?

Question

SQL

Good afternoon!

There is a post entity post

id | body ....

and a many-to-one associated likes-dislikes entity

id | post_id | direction .....

The direction field is to store an enum with @Enumerated(EnumType.STRING), i.e. it contains two possible string values ​​'UP' or 'DOWN'

We need to make a selection of posts ordered by rating , which is calculated conditionally as count(UP) - count(DOWN)

That is, something like

select p, ( count(***) - count(***) ) as post_rate 
from Post p join Vote v on p.id = v.post.id 
group by p.id order by post_rate desc

, only I do not have enough experience / knowledge to write such a request. And despite the fact that the task is typical, in general, I could not google anything.

It would be much easier if instead of 'UP' and 'DOWN' there were 1 and -1, sum() could be bypassed, but the database model has long been formed.

Please, tell me how to solve the problem. Maybe there is a link to similar tasks. Thank you!

Answer 1

If direct contains only 'UP' and 'DOWN', then in MySQL you can calculate the rating like this:
SUM(IF(v.direct = 'UP', 1, -1)) AS post_rate
A more universal version that works in other DBMS:

SUM(CASE WHEN v.direct = 'UP' THEN 1 ELSE -1 END) AS post_rate

If there can be more options, the expression is more cumbersome:

SUM(CASE WHEN v.direct = 'UP' THEN 1 WHEN v.direct = 'DOWN' THEN -1 ELSE 0 END) AS post_rate