Как упоряочить выборку по количеству связанных сущностей?

Question

[piatachki]

Добрый день!

Есть сущность поста post

id | body ....

и много-к-одному связанная сущность лайков-дизлайков

id | post_id | direction .....

Поле direction хранить enum с @Enumerated(EnumType.STRING), то есть содержит два возможных строковых значения 'UP' или 'DOWN'

Нужно сделать выборку постов, упорядоченную по рейтингу, который рассчитывается условно как count(UP) - count(DOWN)

То есть что-то типа

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

, только опыта/знаний написать подобный запрос у меня не хватает. И несмотря на то, что задача типовая, в общем-то, нагуглить ничего не сумел.

Было бы значительно проще, если бы вместо 'UP' и 'DOWN' были 1 и -1, можно было бы sum() обойтись, но модель БД уже давно сформирована.

Пожалуйста, подскажите, как решить задачу. Может есть ссылка на подобные задачи. Спасибо!

Comments

[tukreb]

Было бы значительно проще, если бы вместо 'UP' и 'DOWN' были 1 и -1, можно было бы sum() обойтись, но модель БД уже давно сформирована.

так присвойте им через тот же case значения 1 или -1, в чём проблема?

Answer 1

[Андрей Ежгуров]

Если direct содержит только 'UP' и 'DOWN', то в MySQL подсчитать рейтинг можно так:
SUM(IF(v.direct = 'UP', 1, -1)) AS post_rate
Более универсальный вариант, работающий и в других СУБД:

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

Если вариантов может быть больше, выражение более громоздкое:

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

Answer 2

[Slava Rozhnev]

Следующий запрос решает Вашу проблемуЖ

select posts.id, posts.body, sum(
  case direction 
  	when 'UP' then 1
  	when 'DOWN' then -1
  	else 0
  end) likes
from posts 
left join likes on likes.post_id = posts.id
group by posts.id, posts.body
order by likes desc
;

Здесь можно зпустить SQL запрос и проверить результат

Ещё вариант, используя COUNT вместо SUM

select posts.id, posts.body, (
  count(nullif(direction, 'DOWN')) - count(nullif(direction, 'UP'))) likes
from posts 
left join likes on likes.post_id = posts.id
group by posts.id, posts.body
order by likes desc
;

выполнить SQL запрос