主页 / dba / 问题 / 316975
Accepted
Alex Tbk
Asked: 2022-09-16 21:17:01 +0800 CST

在 Postgres 11 中的第一个匹配项上停止递归查询

  • 772

我在 Postgres 中的递归查询有点挣扎。

鉴于这些表：

create table resource_v3
(
    id            uuid not null primary key,
    name          varchar(255),
    resource_id   uuid,
    resource_type varchar(255),
    tenant_id     uuid
);

和

create table resource_associations
(
    parent_id uuid not null
    child_id  uuid not null
    primary key (parent_id, child_id)
);

鉴于这些表，我构建了以下结构

在此处输入图像描述

我要存档的是获取 user1 所在的所有团队或组织。我只需要第一个匹配项，因此给定 user1 递归应该在“团队服务技术人员”和“ORG：Seepex”处停止。它不应该返回“ORG:CS”而不是“ORG:Seepex-UK”

我的查询：

WITH RECURSIVE c AS (
   SELECT v.id from resource_v3 v where v.resource_id = '8a84bf66-6dc4-43c7-ace1-d104fddaa1be'

   UNION ALL

   SELECT sa.parent_id
   FROM resource_associations AS sa JOIN c ON c.id = sa.child_id
       JOIN resource_v3 rv3 on c.id = rv3.id
    WHERE rv3.resource_type != 'TEAM' OR rv3.resource_type != 'ORGANISATION'
)
SELECT r.* FROM c JOIN resource_v3 r ON c.id=r.id;

如我所料返回更多数据：

在此处输入图像描述

我在这里做错了什么或者你将如何解决这个问题？似乎我几乎在那里，查询应该在第一次命中时停止向上移动。

我的最终目标是获取给定用户所在的 TEAM 或 ORGANIZATION 的所有子元素。因此，逻辑计划是使用上面的查询获取组织/团队，然后从那里递归获取子元素，这相当容易并且运作良好。

postgresql recursive

1 个回答

  1. Best Answer

    尝试以下查询。WHERE c.resource_type NOT IN ('TEAM', 'ORGANISATION')诀窍是如果您还没有通过在 CTE 的递归部分中使用找到 TEAM 或 ORGANIZATION 项，则继续向上链。

    WHERE c.resource_type IN ('TEAM', 'ORGANISATION')如果您只想返回找到的顶部（TEAM 或 ORGANIZATION）项目而不返回起点（User1）或不同资源类型的可能中间项目，则需要外部查询。

    WITH RECURSIVE c AS (
   SELECT v.id, v.resource_type 
   from resource_v3 v 
   where v.resource_id = '8a84bf66-6dc4-43c7-ace1-d104fddaa1be'

   UNION ALL

   SELECT sa.parent_id, rv3.resource_type
   FROM resource_associations AS sa 
        JOIN c ON c.id = sa.child_id
        JOIN resource_v3 rv3 on sa.parent_id = rv3.id
   WHERE c.resource_type NOT IN ('TEAM', 'ORGANISATION')
)
SELECT r.* 
FROM c JOIN resource_v3 r ON c.id = r.id
WHERE c.resource_type IN ('TEAM', 'ORGANISATION') ;
    • 3

相关问题