postgres对同一张表进行递归查询

发布于 2024-11-07 11:58:29 字数 759 浏览 0 评论 0原文

我现在花了几乎一天的时间，看来我做错了什么。好的，这是关系： document_urls( doc_id , url_id)

我想要做的是构建一种图表，该图表将显示通过其网址从文档生成的所有子项。例子从 document_urls 中选择 *，其中 doc_id=1

doc_id url_id
1 2
1 3

如果我选择 url_id=3 或 2 的所有文档，我会找到从 document_urls 中选择 *，其中 url_id=2 或 url_id=3

doc_id url_id
1 2
1 3
2 3

现在我对文档 2 进行相同的练习，因为我们涵盖了文档 1 的所有链接等等。

现在这是我的递归查询

WITH  RECURSIVE generate_links(document_id,url_id) as(  
    select document_id,url_id from document_urls where document_id=1 
UNION ALL
    select du.document_id,du.url_id from generate_links gl,document_urls du
    where gl.url_id=du.url_id 
)

SELECT * FROM generate_links GROUP BY url_id,document_id limit 10;

原文

i spent almost a day on it now and it seems like i am doing something wrong.
ok , here is the relation:
document_urls( doc_id , url_id)

what i want to do is to build a sorte of graph that will show all the children that has been generated from a document through on of his urls.
example
select * from document_urls where doc_id=1

doc_id url_id
1 2
1 3

if i select all the document with url_id=3 or 2 i will find
select * from document_urls where url_id=2 or url_id=3

doc_id url_id
1 2
1 3
2 3

now i do the same exercise with document 2 since we covered all links of document 1 and so forth.

here is my recursive query now

WITH  RECURSIVE generate_links(document_id,url_id) as(  
    select document_id,url_id from document_urls where document_id=1 
UNION ALL
    select du.document_id,du.url_id from generate_links gl,document_urls du
    where gl.url_id=du.url_id 
)

SELECT * FROM generate_links GROUP BY url_id,document_id limit 10;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

陌路黄昏 2024-11-14 11:58:29

我认为您想将 where document_id=1 移动到查询的下部。

不过，这样做时要小心，因为递归查询不会将约束注入到 with 语句中。换句话说，它实际上会 seq 扫描您的整个表，递归地构建每种可能性并过滤掉您需要的那些。

在实践中，使用 sql 函数会更好，即如下所示：

create or replace function gen_links(int) returns table (doc_id int, doc_url text) as $
WITH  RECURSIVE generate_links(document_id,url_id) as(  
    select document_id,url_id from document_urls where document_id=$1
UNION ALL
    select du.document_id,du.url_id from generate_links gl,document_urls du
    where gl.url_id=du.url_id 
)

SELECT * FROM generate_links GROUP BY url_id,document_id;
$ language sql stable;

I take it you want to move your where document_id=1 into the lower part of the query.

Be wary about doing so, however, because a recursive query does not inject the constraint into the with statement. In other words, it'll actually seq scan your whole table, recursively build every possibility and filter out those you need.

You'll be better off with an sql function in practice, i.e. something like this:

create or replace function gen_links(int) returns table (doc_id int, doc_url text) as $
WITH  RECURSIVE generate_links(document_id,url_id) as(  
    select document_id,url_id from document_urls where document_id=$1
UNION ALL
    select du.document_id,du.url_id from generate_links gl,document_urls du
    where gl.url_id=du.url_id 
)

SELECT * FROM generate_links GROUP BY url_id,document_id;
$ language sql stable;

回复收藏 0 原文

~没有更多了~