如何用SQLAlchemy实现按上级姓名排序的LEFT JOIN查询？

前提假设 #

先假设你的User ORM模型定义如下（需与数据库表结构匹配）：

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    superior_id = Column(Integer)
    first_name = Column(String)
    last_name = Column(String)

方式一：ORM Query API实现 #

通过aliased创建表别名，实现自连接和排序：

from sqlalchemy.orm import aliased

# 为User表创建别名t2（t1直接用原模型即可）
t2 = aliased(User)

# 构建查询
query = (
    session.query(User.superior_id)  # 对应SQL中的t1.superior_id
    .outerjoin(t2, User.superior_id == t2.id)  # 对应LEFT JOIN逻辑
    .order_by(t2.first_name, t2.last_name)
)

# 执行查询获取结果
results = query.all()

方式二：SQLAlchemy Core实现 #

如果直接使用Core层操作数据库表：

from sqlalchemy import select, Table, MetaData

# 加载数据库表结构
metadata = MetaData()
user_table = Table("user", metadata, autoload_with=engine)

# 创建两个表别名
t1 = user_table.alias("t1")
t2 = user_table.alias("t2")

# 构建SQL语句
stmt = (
    select(t1.c.superior_id)
    .select_from(t1.outerjoin(t2, t1.c.superior_id == t2.c.id))
    .order_by(t2.c.first_name, t2.c.last_name)
)

# 执行查询
with engine.connect() as conn:
    results = conn.execute(stmt).fetchall()

关键注意点 #

• 必须用aliased（ORM）或.alias()（Core）创建表别名，避免自连接时字段冲突
• outerjoin对应SQL中的LEFT JOIN，确保即使没有上级的用户数据也会被保留
• 确保模型/表的字段名与SQL中的列名完全一致（比如superior_id、first_name）

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