如何使用PostgreSQL按路径筛选数据

Question

我在数据库中有一些资源，这些资源继承了它们的子资源。当我查询一个资源时，我需要能够获取到继承的资源。我有一个名为path的字段，我计划使用它。 path始终包含与我们当前处理的资源相关的所有资源的完整路径。

例如:

+-----------------------------------------+
| id | res_id    | path                   |
|-----------------------------------------|
| 1  | res_1     | res_1                  |
| 2  | res_1.1   | res_1.res_1.1          |
| 3  | res_1.2   | res_1.res_1.2          |
| 4  | res_1.1.1 | res_1.res_1.1.res_1.1.1|
+-----------------------------------------+

如果我查询res_1.1，那么我也必须获取res_1，因为它是res_1.1的父级。如果我获取res_1.1.1，那么我也必须获取行1和行2，因为它们包含在res_1.1.1的路径中。希望能得到一些建议如何在Postgres中实现此功能。我还在使用sqlmodel编写查询，如果这是重要信息，请告诉我。

编辑。抱歉介绍不够清晰，参数path已经是sqlalchemy Ltree -field。希望这可以让事情变得更简单？

- lr_optim

---

请说明您的实际表定义（CREATE TABLE脚本）和您使用的Postgres版本，以澄清。 - Erwin Brandstetter

Answer 1

使用类型ltree

事实证明，您正在使用附加模块ltree。点（.）是不含糊的分隔符。假设path是类型ltree（您还没有澄清），这简化了任务。有专用运算符，例如：

ltree @> ltree → boolean

左参数是否为右参数的祖先（或相等）？

因此：

SELECT t.*
FROM   tbl_ltree t1   
JOIN   tbl_ltree t ON t.path @> t1.path
WHERE  t1.res_id = 'res_1_1_1';  -- your search term here

ltree 还为 GiST 索引提供了操作符类：

CREATE INDEX tbl_ltree_path_gist_idx ON tbl USING GIST (path);

可以被上面的查询使用。此外，您还可以在res_id上拥有另一个B-tree索引。

res_id是完全冗余的，可以忽略甚至删除。相反，我们可以在path上使用另一个ltree运算符：

ltree ~ lquery → boolean

ltree是否与lquery匹配？

关于lquery类型。

SELECT *
FROM   tbl_ltree   
WHERE  path @> (SELECT path FROM tbl_ltree WHERE path ~ '*.res_1_1_1'::lquery);

db<>fiddle 这里

只需要使用GiST索引。

使用text类型

如果使用text而不是ltree（您还没有澄清），以下是其中一种方法：

SELECT t.*
FROM  (
   SELECT unnest(string_to_array(path, '.')) AS res_id
   FROM   tbl_txt
   WHERE  res_id = 'res_1_1_1'  -- your search term here
   ) t1
JOIN  tbl_txt t USING (res_id);

db<>fiddle 在这里

t1 子查询将 path 拆分为其构建块，然后连接到另一个 tbl_txt 的实例。

- Erwin Brandstetter

---

@lr_optim：为了避免耗时的误解，请在提出任何未来的Postgres问题时，披露正在使用的版本和相关表定义。 - Erwin Brandstetter

Answer 2

例如，使用LIKE谓词来查找部分匹配path的行。 查询是：

SELECT t1.* FROM resources t1
WHERE
  (
    SELECT t2.path FROM resources t2
    WHERE t2.res_id = 'res_1.1'
  )
  LIKE t1.path||'%';

如果路径（path）没有正确匹配，就添加一个点（.）。

SELECT t1.* FROM resources t1
WHERE
  (
    SELECT t2.path||'.' FROM resources t2
    WHERE t2.res_id = 'res_1.1'
  )
  LIKE t1.path||'.%';

DB Fiddle

（数据库小提琴）

- etsuhisa