SQLAlchemy中如何通过关联查询按条件获取多个SessionData.value值

你需要的是行转列的关联查询实现，下面给出两种性能和兼容性都不错的实现方式：

方案1：条件聚合（推荐，代码更简洁） #

核心逻辑是通过一次关联全量匹配的SessionData数据，再通过条件聚合提取指定key的value，不需要多次关联表：

from sqlalchemy import func, case

stmt = select(
    ActionInfo.id,
    # 提取username字段，兼容所有数据库的写法
    func.max(
        case((SessionData.key == "username", SessionData.value), else_=None)
    ).label("username"),
    # 提取country字段
    func.max(
        case((SessionData.key == "country", SessionData.value), else_=None)
    ).label("country"),
).outerjoin(
    SessionInfo, SessionInfo.id == ActionInfo.session_id
).outerjoin(
    SessionLink, SessionLink.info_id == SessionInfo.id
).outerjoin(
    SessionData, SessionData.id == SessionLink.data_id
).group_by(
    ActionInfo.id
)

如果你的数据库是MySQL，可以简化case为func.if_，写法更短：

func.max(func.if_(SessionData.key == "username", SessionData.value, None)).label("username")

方案2：别名多关联 #

如果不想用group by，也可以给不同key的SessionData定义别名，分别关联：

from sqlalchemy.orm import aliased
from sqlalchemy import and_

# 给不同key的SessionData定义独立别名
SessionDataUsername = aliased(SessionData)
SessionDataCountry = aliased(SessionData)

stmt = select(
    ActionInfo.id,
    SessionDataUsername.value.label("username"),
    SessionDataCountry.value.label("country"),
).outerjoin(
    SessionInfo, SessionInfo.id == ActionInfo.session_id
).outerjoin(
    SessionLink, SessionLink.info_id == SessionInfo.id
).outerjoin(
    SessionDataUsername, 
    and_(SessionDataUsername.id == SessionLink.data_id, SessionDataUsername.key == "username")
).outerjoin(
    SessionDataCountry, 
    and_(SessionDataCountry.id == SessionLink.data_id, SessionDataCountry.key == "country")
)

说明 #

1. 两种方案都保留了左外连接的特性，没有对应SessionData记录的行，对应的username/country字段会自动返回Null，符合你的需求。
2. 你后续要测试内连接效果时，只需要把对应的outerjoin改为join即可，其他逻辑不需要调整。
3. 两种方案的性能和你之前单key关联查询基本持平，对比原来的子查询写法有7倍左右的性能提升。

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