python - 如何使用 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 。我希望这能让事情变得简单一点？

Answer 1

使用类型 ltree

原来你正在使用附加模块 ltree .点 (.) 是明确的分隔符。假设 path 是 ltree 类型(您还没有明确说明)可以简化任务。有dedicated operators喜欢:

ltree @> ltree → boolean

Is left argument an ancestor of right (or equal)?

所以:

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 树索引。

res_id 是完全多余的，可以忽略甚至丢弃。我们可以在 path 上使用另一个 ltree 运算符:

ltree ~ lquery → boolean

Does ltree match lquery?

About the lquery type.

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

db<> fiddle here

只需要 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 here

子查询 t1 将 path 拆分为其构建 block 。然后加入 tbl_txt 的另一个实例。