Как передать переменную подвыборке в представлении
У меня есть таблица сообщений, post_likes, и мне нужен запрос, который даст мне как общее количество лайков для сообщений, так и также для заданных лайков конкретного пользователя для этих сообщений. Это означает, что мне нужен хороший способ указать MY_USER_ID в качестве входных данных.
Вот запрос, который работает
create view post_view as
select post.id,
coalesce(sum(post_like.score),0) as score,
(
select score
from post_like
where post.id = post_like.post_id
and post_like.fedi_user_id = MY_USER_ID
) as my_vote,
from post
left join post_like on post.id = post_like.post_id
group by post.id;
НО мой ORM (ржавый дизель) не позволяет мне устанавливать или запрашивать это необходимое поле MY_USER_ID, так как это подзапрос.
Мне бы очень хотелось сделать что-то вроде:
select *
from post_view
where my_user_id = 'X';
Ответы 3
вы можете переместить это условие в пункт on
create view post_view as
select post.id,
coalesce(sum(post_like.score),0) as score
from post
left join post_like
on post.id = post_like.post_id and post_like.fedi_user_id = MY_USER_ID
group by post.id;
Разоблачить my_user_id в предложении select представления
-- get all of the user's score on all post, regardless of the user liking the post or not
create view post_view as
select
u.id as my_user_id,
p.id as post_id,
sum(pl.score) over (partition by p.id) as score,
coalesce(pl.score, 0) as my_vote -- each u.id's vote
from user u
cross join post p
left join post_like pl on u.id = pl.fedi_user_id and p.id = pl.post_id;
select * from post_view where my_user_id = 'X';
ОБНОВИТЬ
Это может получить баллы за сообщение, даже если пользователь не указан
create view post_view as
with all_post as
(
select
p.id as post_id,
sum(pl.score) as score
from post p
left join post_like pl on p.id = pl.post_id
group by p.id
)
select
u.id as my_user_id,
ap.post_id,
ap.score,
coalesce(pl.score, 0) as my_vote
from user u
cross join all_post ap
left join post_like pl on u.id = pl.fedi_user_id and ap.post_id = pl.post_id
union all
select
'' as my_user_id,
ap.post_id,
ap.score,
0 as my_vote
from all_post ap
;
select * from post_view where my_user_id = 'X';
Если ни один пользователь не передан, выберите запрос, обозначенный my_user_id из ''
select * from post_view where my_user_id = '';
Это действительно отличный ответ и НАСТОЛЬКО близко... но он не подходит для случая, когда мне также нужно получить сумму, когда я не даю пользователю. Я думаю, что то, о чем я прошу, невозможно в sql, и это лучшее, что я могу получить, и отвечает на мой вопрос: он перемещает пользовательские настройки из подзапроса. Ваше здоровье!
@thouliha внесла изменения, поэтому, когда нет переданного пользователя, он все равно будет показывать все оценки публикации.
Спасибо, я не подумал включить эту информацию в союз! Начиная с вашего ответа, я просто сгруппировал все остальные поля и установил для user_id и my_vote значения null для моего ORM, но это тоже работает!
Вы можете переместить логику в select, используя условную агрегацию:
select p.id,
coalesce(sum(pl.score), 0) as score,
sum( (pl.fedi_user_id = MY_USER_ID)::int ) as my_vote
from post p left join
post_like pl
on p.id = pl.post_id
group by p.id;
Это оставляет меня в том же положении, что и раньше, и я не могу установить MY_USER_ID вне представления.
@тулиха . . . Я боялся, что это может быть то, что вы хотели сделать. Вы не можете определить представление с помощью переменной. Вам нужна определяемая пользователем табличная функция: postgresql.org/docs/11/sql-createfunction.html.
Вы не можете передать параметр в представление. Вам нужно будет использовать функцию возврата набора, которая имеет параметр и возвращает результат запроса.