优化SQLAlchemy多查询为单查询：统计用户所属聊天室在线离线人数

原代码通过循环每个聊天室分别查询在线和离线用户数，会发起多次数据库请求，效率低下。可以利用PostgreSQL的条件聚合功能，用一次查询完成所有统计。

优化后的get_chatrooms_and_user_counts方法如下：

async def get_chatrooms_and_user_counts(self, db: AsyncSession):
    stmt = (
        select(
            user_chatroom_table.c.chatroom_id,
            func.sum(case((Users.online == True, 1), else_=0)).label("online"),
            func.sum(case((Users.online == False, 1), else_=0)).label("offline")
        )
        .select_from(user_chatroom_table)
        # 先筛选当前用户所属的聊天室
        .where(user_chatroom_table.c.user_id == self.id)
        # 关联该聊天室下的所有用户
        .join(
            user_chatroom_table,
            user_chatroom_table.c.chatroom_id == user_chatroom_table.c.chatroom_id,
            isouter=True
        )
        .join(Users, user_chatroom_table.c.user_id == Users.id)
        # 按聊天室ID分组统计
        .group_by(user_chatroom_table.c.chatroom_id)
    )
    result = await db.execute(stmt)
    chatrooms_info = [
        {
            "chatroom_id": str(row.chatroom_id),
            "online": row.online,
            "offline": row.offline
        }
        for row in result.all()
    ]
    return chatrooms_info

逻辑说明： #

1. 条件聚合统计：用func.sum配合case语句，对每个用户的online状态做判断，分别累加在线、离线用户的计数，一次得到每个聊天室的两类用户总数。
2. 单查询完成全流程：直接通过当前用户ID筛选出所属聊天室，再关联这些聊天室的所有用户数据，最后按聊天室ID分组，避免循环发起多次查询。
3. 简化代码结构：优化后无需再调用get_user_chatrooms方法，所有逻辑在单条查询内完成，大幅减少数据库交互次数。

另外，也可以利用PostgreSQL中布尔值可自动转为整数的特性简化统计代码：

func.sum(cast(Users.online, Integer)).label("online"),
func.sum(cast(not_(Users.online), Integer)).label("offline")

这种写法更简洁，统计效果和case语句完全一致。

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