如何在SQLModel ORM中实现带子查询的自连接？

问题背景 #

需要在SQLModel中实现与以下PostgreSQL SQL等效的自连接查询：

SELECT tt.id, ttp.parent_name, tt.name
  FROM target_table tt, 
       (select DISTINCT bb.parent_id, bbref.name parent_name
          from target_table tt, target_table ttref
         WHERE tt.parent_id is not null 
           AND tt.parent_id = ttref.id
         order by 1) ttp
 WHERE tt.parent_id = ttp.parent_id
ORDER BY 1

已定义TargetTable模型且能正常读写，但构造查询时遇到语法与逻辑问题，尝试的错误代码如下：

select(TargetTable).join(select(TargetTable.label('tt')).join(
        (TargetTable.label('ttref').filter(tt.parent_id != NULL, tt.parent_id == ttref.id)
        ).label(ttp)).filter(tt.parent_id = ttp.parent_id)

解决方案 #

1. 构造子查询 #

先给自连接的表实例打标签区分，再构建去重的子查询：

# 给自连接的两个表实例打标签，避免名称冲突
tt_sub = TargetTable.label("tt_sub")
ttref_sub = TargetTable.label("ttref_sub")

# 构造子查询：获取去重的parent_id及对应的父节点名称
subquery = (
    select(
        tt_sub.parent_id,
        ttref_sub.name.label("parent_name")
    )
    .where(tt_sub.parent_id.is_not(None))
    .where(tt_sub.parent_id == ttref_sub.id)
    .distinct()
    .order_by(tt_sub.parent_id)
    .subquery(name="ttp")
)

2. 关联主查询与子查询 #

将主表与子查询关联，选择目标字段：

# 主表实例打标签
tt_main = TargetTable.label("tt_main")

# 构建最终查询语句
final_query = (
    select(
        tt_main.id,
        subquery.c.parent_name,
        tt_main.name
    )
    .join(subquery, tt_main.parent_id == subquery.c.parent_id)
    .order_by(tt_main.id)
)

3. 执行查询 #

在会话中执行并处理结果：

with Session(engine) as session:
    results = session.exec(final_query).all()
    for res in results:
        # 结果为元组，对应(id, parent_name, name)
        print(f"ID: {res[0]}, 父节点名称: {res[1]}, 节点名称: {res[2]}")

关键注意点 #

• 必须用label()给自连接的表实例打标签，避免表名冲突
• 子查询需通过subquery()转换为可关联对象，用subquery.c.字段名访问子查询字段
• SQLModel中判断非空用is_not(None)，而非!= NULL
• 条件判断使用Python的==，而非SQL的=

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