Postgres WITH子查询使用关系id

Question

我知道，这个查询(这是不正确的，只是一个例子)可以用其他方式编写。但是，当存在这样的关系m.user_id = u.id时，我对使用WITH很感兴趣。这可能吗?https://www.postgresql.org/docs/current/queries-with.html -看起来WITH只用于准备好的数据，而不是动态的。

with last_message as (
select m.message 
from messages m 
where m.user_id = u.id 
order by m.id desc 
limit 1
)
select u.*, last_message
from users u
where last_message = 'some_message';

注:不明白，为什么Postgres限制我们在子查询中使用变量(作为将要执行的语句)，这样我们就可以很容易地在select和where子句中使用这个变量。目前，我必须从select子句复制子查询，并在where子句(

)中使用它。

Answer 1

如果我对你的问题查询-与子查询复制在WHERE子句中类似于下面的

select u.*, m.last_message
from users u
cross join lateral (
select  m.message last_message
from messages m 
where m.user_id = u.id 
order by m.id desc 
limit 1
) m  
where (
select m.message 
from messages m 
where m.user_id = u.id 
order by m.id desc 
limit 1
) = 'some_message';

这确实不是首选的解决方案。这不仅违反了DRY原则，而且子查询可以有效地为每个用户执行两次。

不幸的是(或者更好的不出所料)，您不能像示例中那样使用WITH子查询将与其他表相关联。这直接导致

ERROR: missing FROM-clause entry for table "u"

替代方案是什么?定义CTE，以便它为所有用户生成最后一条消息，并将其与查询连接起来。下面是使用distinct on

的示例

with last_message as (
select distinct on (m.user_id) user_id, m.message last_message 
from messages m 
order by m.user_id, m.id desc 
)
select u.*, m.last_message
from users u
join last_message m on u.id = m.user_id
where m.last_message = 'some_message';

作为另一个选项，您可以定义一个过滤最后消息的视图，并在join

中使用它

create view last_message as
select distinct on (m.user_id) user_id, m.message last_message 
from messages m 
order by m.user_id, m.id desc;

select u.*, m.last_message
from users u
join last_message m on u.id = m.user_id
where m.last_message = 'some_message';