在Postgres 11中突破递归查询

Question

我在从树结构中获取正确数据时遇到了一些困难，在树结构中，我只希望分支匹配条件中的第一个节点是查询的结果。

我有一张域表：

create table domains(
    id bigint,
    domain_name varchar,
    parent_id bigint,
    valid boolean
)

insert into domains values
    (1, 'example.com', null, false),
    (2, 'a.example.com', 1, true),
    (3, 'b.example.com', 1, false),
    (4, 'c.b.example.com', 3, true),
    (5, 'd.a.example.com', 2, true)

这给出了这棵树，该树的有效域为绿色：

基于此，我希望a.example.com和c.b.example.com作为有效域返回。如果顶级域example.com有效，则只应返回该域。(“有效”只是在其他地方确定的标志。)

我有一个递归查询，它获取整个域树：

WITH RECURSIVE valid_domains AS (
  SELECT id, domain_name, valid FROM domains
  WHERE parent_id IS NULL

  UNION ALL

  SELECT d.id, d.domain_name, d.valid FROM domains d
  JOIN valid_domains vd ON d.parent_id = vd.id
)
SELECT * FROM valid_domains

...but --那么我的绊脚石就是弄清楚如何在有效域上过滤，当我点击一个有效的分支时，它就会中断，而不会在它下面返回任何有效的域。

我看过窗口函数，但到目前为止还未能将其与递归查询相结合。我是不是采取了错误的方法，使这个递归在第一？

Answer 1

WITH RECURSIVE cte AS (
  SELECT id, domain_name, valid 
  FROM domains
  WHERE parent_id IS NULL
UNION ALL
  SELECT domains.id, domains.domain_name, domains.valid 
  FROM domains
  JOIN cte ON domains.parent_id = cte.id
  WHERE NOT cte.valid -- stop recursion when valid node reached
)

SELECT id, domain_name
FROM cte
WHERE valid

小提琴

Answer 2

你差点就到了：

WITH RECURSIVE valid_domains AS (
  SELECT id, domain_name, valid FROM domains
  WHERE parent_id IS NULL

  UNION ALL

  SELECT d.id, d.domain_name, d.valid FROM domains d
  JOIN valid_domains vd ON d.parent_id = vd.id
)
SELECT *
FROM valid_domains
WHERE valid
LIMIT 1;

我想您只需要过滤有效的答案，然后使用LIMIT只获得第一个结果.

不过，您可能需要添加一个ORDER BY子句.