PostgreSQL中如何使用lquery过滤任意位置含指定值的ltree路径数据

原有写法的问题 #

你之前使用的!res_1.*匹配规则，仅会匹配根节点（路径第一个标签）为res_1的记录：

• 模式中res_1前没有通配符，默认从路径起始位置开始匹配
• 只能过滤res_1、res_1.res_2这类res_1在路径开头的记录，无法识别中间、末尾出现res_1的路径（比如res_0.res_1.res_2、res_0.res_1）

正确实现方案 #

LQUERY语法中，*是通配符，可匹配0个或多个任意标签。要匹配res_1出现在路径任意位置的情况，匹配模式需要写成*.res_1.*：

• 前缀*.匹配res_1之前的0到多层任意标签（覆盖res_1在开头、中间的场景）
• 后缀.*匹配res_1之后的0到多层任意标签（覆盖res_1在中间、末尾的场景）

你可以任选以下两种写法实现排除逻辑：

写法1：在LQUERY模式内取反 #

直接修改lquery字符串即可，代码改动最小：

from sqlalchemy_utils.types.ltree import LQUERY
from sqlalchemy.sql import expression

# 匹配所有不包含任意位置res_1标签的路径
custom_lquery = '!*.res_1.*'
stmnt = stmnt.filter(MyModel.path.lquery(expression.cast(custom_lquery, LQUERY)))

写法2：用SQLAlchemy取反操作符（可读性更高） #

如果担心lquery内!的作用范围不清晰，可以先匹配「包含res_1」的记录，再用SQLAlchemy的~（非）操作符取反，逻辑更直观：

from sqlalchemy_utils.types.ltree import LQUERY
from sqlalchemy.sql import expression

# 先匹配任意位置含res_1的路径，再取反排除
contain_res1_lquery = '*.res_1.*'
stmnt = stmnt.filter(
    ~MyModel.path.lquery(expression.cast(contain_res1_lquery, LQUERY))
)

匹配说明 #

该规则是按ltree的完整标签维度匹配，不会出现子串误匹配问题：

• 会正确命中排除：res_1、a.res_1、res_1.b、a.res_1.b
• 不会误排除：res_10、a.res_11、xres_1.b这类标签名包含res_1但不是完整标签的路径

内容的提问来源于stack exchange，提问作者lr_optim