根据SQLAlchemy中的另一个值从联接中选择多个值

Question

我对SQLAlchemy有一个相当繁重的查询，我正试图对它进行一些优化，但我正在努力处理连接，因为我对它了解不多。我的一个非常小的测试显示，选择比连接慢7倍，因此它可能会有相当大的速度提升

以下是相关表格及其关系：

• ActionInfo (id, session_id = SessionInfo.id)
• SessionInfo (id)
• SessionLink (info_id = SessionInfo.id, data_id = SessionData.id)
• SessionData (id, key, value)

我基本上想阅读SessionData.value，其中SessionData.key等于某物，从ActionInfo中选择

以下是我目前的做事方式：

stmt = select(
    ActionInfo.id,
    select(SessionData.value).where(
        SessionData.key == 'username',
        SessionLink.data_id == SessionData.id,
        SessionLink.info_id == ActionInfo.session_id,
    ).label('username'),
    select(SessionData.value).where(
        SessionData.key == 'country',
        SessionLink.data_id == SessionData.id,
        SessionLink.info_id == ActionInfo.session_id,
    ).label('country'),
)

在进行上述速度测试时，我得到了一个单独的连接，但通过此方法，我显然仅限于1个值：

stmt = select(
    ActionInfo.id,
    SessionData.value.label('country')
).filter(
    SessionData.key == 'country'
).outerjoin(SessionInfo).outerjoin(SessionLink).outerjoin(SessionData)

我该如何调整它以得到这样的结果

stmt = select(
    ActionInfo.id,
    select(SessionData.value).where(SessionData.key=='username').label('username'),
    select(SessionData.value).where(SessionData.key=='country').label('country'),
).outerjoin(SessionInfo).outerjoin(SessionLink).outerjoin(SessionData)

如果有帮助的话，这是SQLAlchemy生成的连接代码：

SELECT action_info.id
FROM action_info LEFT OUTER JOIN session_info ON session_info.id = action_info.session_id LEFT OUTER JOIN session_link ON session_info.id = session_link.info_id LEFT OUTER JOIN session_data ON session_data.id = session_link.data_id

作为补充说明，我假设我想要一个左外部联接，因为我仍然希望包含任何缺少SessionData记录的记录。一旦我有这个工作，虽然我会测试什么样的区别，一个内部连接，以确保

Answer 1

代码如下：

keys = ["username", "country", "gender"]
q = select(ActionInfo.id).join(SessionInfo)
for key in keys:
    SD = aliased(SessionData)
    SL = aliased(SessionLink)
    q = (
        q.outerjoin(SL, SessionInfo.id == SL.info_id)
        .outerjoin(SD, and_(SL.data_id == SD.id, SD.key == key))
        .add_columns(SD.value.label(key))
    )

是通用的，可以扩展到不同数量的字段，并应生成类似于以下内容的SQL：

SELECT action_info.id,
       session_data_1.value AS username,
       session_data_2.value AS country,
       session_data_3.value AS gender

FROM   action_info

JOIN   session_info ON session_info.id = action_info.session_id

LEFT OUTER JOIN session_link AS session_link_1 ON session_info.id = session_link_1.info_id
LEFT OUTER JOIN session_data AS session_data_1 ON session_link_1.data_id = session_data_1.id
    AND session_data_1.key = :key_1

LEFT OUTER JOIN session_link AS session_link_2 ON session_info.id = session_link_2.info_id
LEFT OUTER JOIN session_data AS session_data_2 ON session_link_2.data_id = session_data_2.id
    AND session_data_2.key = :key_2

LEFT OUTER JOIN session_link AS session_link_3 ON session_info.id = session_link_3.info_id
LEFT OUTER JOIN session_data AS session_data_3 ON session_link_3.data_id = session_data_3.id
    AND session_data_3.key = :key_3