时间戳

首页 > 解决方案 > 有一个嵌套的项目 ID 和所有者 ID，找出该项目是否属于该所有者

sql - 有一个嵌套的项目 ID 和所有者 ID，找出该项目是否属于该所有者

问题描述

此表称为类别：

id|template_id|name    |entry_count|is_base_template|can_rename|can_delete|section|userId|parentCategoryId|
--|-----------|--------|-----------|----------------|----------|----------|-------|------|----------------|
 1|           |ToDo    |          0|true            |true      |true      |A      |      |                |
 2|           |Notes   |          0|true            |true      |true      |A      |      |                |
 3|          1|ToDo    |          0|false           |true      |true      |A      |     1|                |
 4|          2|Notes   |          0|false           |true      |true      |A      |     1|                |
 5|           |my todos|          0|false           |          |          |A      |      |               3|
 6|          1|ToDo    |          0|false           |true      |true      |A      |     2|                |
 7|          2|Notes   |          0|false           |true      |true      |A      |     2|                |

每个类别都可以有子类别，可以通过名为 的字段来确定我们是在处理类别还是子类别parentCategoryId，它是NULL针对类别而不是NULL针对子类别的。

我知道userId类别 id 就id在该表中。我必须找出具有特定 id（例如 id = 5）的类别是否属于某个用户（例如，具有 id = 1）。

必须有一些技巧可以做到这一点，但我想不出一个。

我的 PostgreSQL 版本是12.3.

编辑-1：

首先，谢谢。

我尝试在本地客户端（DBeaver）和控制台中执行它，但它不起作用。我刚刚将演示更改为类别：

WITH RECURSIVE cte AS (
    SELECT id, userId, parentCategoryId
    FROM category
    WHERE id = 5 --the category of interest

    UNION

    SELECT d.id, d.userId, d."parentCategoryId"
    FROM category d
             INNER JOIN cte ON d.id = cte."parentCategoryId"
)
SELECT TRUE
FROM cte
WHERE userId = 1 --the user of interest
LIMIT 1;

我收到一条错误消息：

SQL Error [42703]: ERROR: column "userid" does not exist
  Hint: Perhaps you meant to reference the column "category.userId".
  Position: 40

编辑-2：

如果我关闭这条线

SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = 'category';

我得到的结果是这样的：

column_name     |
----------------|
parentCategoryId|
template_id     |
userId          |
id              |
entry_count     |
is_base_template|
can_rename      |
can_delete      |
name            |
section         |

标签： sqlpostgresql

解决方案

演示数据：

CREATE TABLE category
(
    "id"               INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    "name"             TEXT    NOT NULL,
    "userId"           INTEGER NULL,
    "parentCategoryId" INTEGER NULL,

    CONSTRAINT category_pkey PRIMARY KEY (id),
    CONSTRAINT cateory_fkey_parent_id FOREIGN KEY ("parentCategoryId") REFERENCES category (id)
);

INSERT INTO category(name, "userId", "parentCategoryId")
VALUES ('ToDo', NULL, NULL);
INSERT INTO category(name, "userId", "parentCategoryId")
VALUES ('Notes', NULL, NULL);
INSERT INTO category(name, "userId", "parentCategoryId")
VALUES ('ToDo', 1, NULL);
INSERT INTO category(name, "userId", "parentCategoryId")
VALUES ('Notes', 1, NULL);
INSERT INTO category(name, "userId", "parentCategoryId")
VALUES ('my_todos', NULL, 3);
INSERT INTO category(name, "userId", "parentCategoryId")
VALUES ('ToDo', 2, NULL);
INSERT INTO category(name, "userId", "parentCategoryId")
VALUES ('Notes', 2, NULL);

您可以使用递归查询来查找您的类别的父树，然后您可以检查用户是否拥有任何父节点：

WITH RECURSIVE cte AS (
    SELECT "id", "userId", "parentCategoryId"
    FROM category
    WHERE id = 5 --the category of interest

    UNION

    SELECT d."id", d."userId", d."parentCategoryId"
    FROM category d
             INNER JOIN cte ON d."id" = cte."parentCategoryId"
)
SELECT TRUE
FROM cte
WHERE "userId" = 1 --the user of interest
LIMIT 1;

请注意，所有列名都被引用 - 这意味着它们区分大小写。

查询的工作原理：

递归查询包含树部分：

  1. 查询的基础（非递归），它检索一些行

  2. UNION 子句，它结合了递归和非递归部分的结果

  3. 递归部分，对从递归和非递归部分返回的每一行执行。

在您的示例数据中，查询的基础返回 row (id=5, user=null, parentCategoryId=3)。该行作为输入传递给递归查询，递归查询将其与category表连接起来。另一个查询返回一个新行(id=3, userId=1, parentCategoryId=null)。再次将此行作为参数传递给递归查询，但是作为parentCategoryId=null，这次查询不返回任何行，因此执行终止。该UNION子句将两行合并为一个结果集。现在，外部select检查是否有任何返回的行包含userId=1并在这种情况下返回 TRUE。

推荐阅读