PostgreSQL递归选择从叶子中查找根元素

我正在为论坛开发一个数据库，其中包含线程和消息。线程以一条没有消息的消息开始parent_id;回复是消息parent_id.

我有一张消息表。每个项目都引用同一个表上的项目，将它们作为父子关系。

create table messages(
  id int,
  title text,
  content text,
  parent_id int
);

现在我用一些数据填充表格：

insert into messages values 
(1, 'One', 'First  thread main post', null), -- First thread
(2, 'Two', 'First thread reply', 1), 
(3, 'Three', 'First thread reply', 2),
(4, 'Four', 'Second thread main post', null), -- Second thread
(5, 'Five', 'Second thread reply', 4),
(6, 'Six', 'Second thread reply', 5),
(7, 'Six', 'Second thread reply', 6),
(8, 'Six', 'Second thread reply', 7),
(9, 'Six', 'Second thread reply', 8); 

知道id对于线程的第一条消息，我可以使用递归查询检索所有回复：

with recursive my_tree as (
    select * from messages 
    where parent_id IS null and id = 4
  union all
    select messages.* from messages 
    join my_tree on messages.parent_id = my_tree.id
) select my_tree.* 
    from my_tree;

这里有一个.

现在，假设我知道id一些答复，我想得到id线程的第一条消息。我该怎么做？

编辑： 我知道我可以从已知的线程中检索所有消息id向上：

with recursive my_tree as (
    select * from messages 
    where id = 9
  union all
    select messages.* from messages 
    join my_tree on messages.id = my_tree.parent_id
) select my_tree.* 
    from my_tree;

Here a 但如果线程由数千条消息组成，效率就会降低。有没有什么方法可以让递归查询到达第一项而不检索所有消息？

以目前的数据结构，除了访问从叶子到根的整个路径之外，没有其他办法。您可以通过仅对关键列进行操作来加快搜索速度：

with recursive my_tree as (
    select id, parent_id
    from messages 
    where id = 9
union all
    select m.id, m.parent_id 
    from messages m
    join my_tree t on m.id = t.parent_id
) 
select m.*
from messages m
join my_tree t using(id)
where t.parent_id is null

如果这个解决方案不令人满意，可以考虑在表中添加一列

alter table messages add root_id int;

使用递归查询填充此列将很容易，并且您将可以立即访问每个节点的根。

另一种方法是使用以下方法更改数据结构l树扩展。 https://www.postgresql.org/docs/current/ltree.html