SQLAlchemy JOIN查询中如何为第一个表设置别名（如PostSQL改POST1）

解决方法 #

要给第一个表设置别名并让返回结果使用自定义键名，需借助SQLAlchemy的aliased()工具创建表别名，同时在select()中给别名实体添加label()指定返回字典的键。

修改后的完整代码 #

from sqlalchemy.orm import aliased  # 导入表别名工具

async def get_join_table(
        db: AsyncSession,
        table: PostSQL | VoteSQL | UserSQL,
        jtable: PostSQL | VoteSQL | UserSQL,
        limit: int = None,
        offset: int = None,
        contains: str = "",
        order=None,
):
    # 给第一个表创建SQL层面的别名，name对应AS后的别名
    table_alias = aliased(table, name="POST1")
    
    query = (
        # 用label指定返回结果中第一个表的键名为POST1
        select(table_alias.label("POST1"), func.count(jtable.post_id).label("votes"))
        # JOIN条件改用别名的字段
        .join(jtable, table_alias.id == jtable.post_id, isouter=True)
        # GROUP BY也使用别名的id
        .group_by(table_alias.id)
        .limit(limit)
        .offset(offset)
        # 过滤条件动态使用别名的title（替换原硬编码的PostSQL）
        .filter(table_alias.title.contains(contains))
        .order_by(order)
    )
    
    response = await db.execute(query)
    posts = response.mappings().all()
    return posts

关键改动说明 #

• aliased(table, name="POST1")：给第一个表创建SQL层面的别名，对应SQL语句中的posts as POST1
• table_alias.label("POST1")：指定返回的映射字典中，第一个表对应的键为POST1，替代原来的类名键
• 所有涉及原表字段的逻辑（JOIN条件、GROUP BY、过滤）均替换为别名引用，保证SQL语法正确

生成的SQL语句（符合期望） #

select
    POST1.* as "POST1",
    count(votes.post_id) as votes
from
    posts as POST1
    left outer join votes ON votes.post_id = POST1.id
group by POST1.id;

返回结果结构（与目标一致） #

{
    "POST1": {
        "content": "GRAND",
        "title": "misery",
        "created_at": "2024-01-27T10:48:35.975442",
        "id": 22,
        "published": true,
        "owner_id": 4,
        "owner": {
            "email": "cc@gmail.com",
            "id": 4,
            "created_at": "2024-01-20T12:44:36.365265"
        }
    },
    "votes": 0
}

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