如何在SQLAlchemy中编写含LEFT JOIN子查询的语句？遇属性错误

目标SQL语句 #

需要实现的原生SQL如下：

SELECT tool.*,IF(b.id,TRUE,NULL) as liked FROM tool LEFT JOIN (
SELECT * FROM user_tool_like WHERE uid = '4d63a2bcae8e11eda9cf68fef7020cbc'
) as b  ON tool.id = b.tid
ORDER BY
tool.create_time DESC 
LIMIT 0,10

尝试代码及错误 #

尝试编写的SQLAlchemy代码：

subquery = db.session.query(UserToolLike).filter_by(uid=user['uid']).subquery()

query = db.session.query(Tool,subquery.id).outerjoin(subquery,Tool.id == subquery.tid)

page = db.paginate(query.order_by(desc(Tool.create_time)))
print(page)

运行后抛出错误：

File "/Users/coderd/Desktop/mypms/toolking/toolking-api/app/api/v1/tool.py", line 48, in get_tool_list
query = db.session.query(Tool,subquery.id).outerjoin(subquery,Tool.id == subquery.tid)
AttributeError: 'Subquery' object has no attribute 'id'

修正方案及正确代码 #

错误原因 #

SQLAlchemy的Subquery对象不能直接通过.访问字段，必须通过c属性（代表column）来引用子查询中的列。同时还需要实现原SQL中IF(b.id, TRUE, NULL)的逻辑。

方案一：完全对应原LEFT JOIN逻辑 #

from sqlalchemy import func, desc

# 子查询只需要tid字段即可，无需查询所有列
subquery = db.session.query(UserToolLike.tid).filter_by(uid=user['uid']).subquery()

# 用func.if实现原生SQL的IF判断逻辑，生成liked字段
liked_expr = func.if(subquery.c.tid.isnot(None), True, None).label('liked')

# 构建查询，通过subquery.c.tid引用子查询字段
query = db.session.query(Tool, liked_expr).outerjoin(
    subquery, Tool.id == subquery.c.tid
).order_by(desc(Tool.create_time))

# 分页，指定页码和每页数量对应原SQL的LIMIT 0,10
page = db.paginate(query, page=1, per_page=10)

方案二：用EXISTS子查询实现（性能更优） #

如果只需要判断是否存在点赞记录，用EXISTS子查询无需JOIN，通常性能更好：

from sqlalchemy import exists, desc

# 构建exists子查询，判断当前tool是否被该用户点赞
liked_subquery = exists().where(
    UserToolLike.tid == Tool.id,
    UserToolLike.uid == user['uid']
)
# 将exists结果转为布尔值并命名为liked
liked_expr = liked_subquery.label('liked')

# 直接查询Tool和liked字段，无需JOIN
query = db.session.query(Tool, liked_expr).order_by(desc(Tool.create_time))

page = db.paginate(query, page=1, per_page=10)

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