如何在Drizzle ORM中查询好友列表时避免N+1查询问题？

核心思路是把好友状态的判断逻辑嵌入主查询，用子查询一次性获取所有所需数据，替代循环调用checkFriendStatus的低效做法。

修改后的完整代码 #

import { sql, or, and, eq, ne } from 'drizzle-orm';
// 请确保已导入对应数据表：friendshipsTable, usersTable, friendRequestsTable

export const getFriendList = async (sessionUserId: number, userId: number) => {
  const friendList = await db
    .select({
      id: usersTable.id,
      name: usersTable.name,
      username: usersTable.username,
      avatar: usersTable.avatar,
      created_at: usersTable.created_at,
      // 判断当前会话用户与好友是否为已确认的好友关系
      isFriend: sql<boolean>`EXISTS (
        SELECT 1 FROM ${friendshipsTable}
        WHERE (
          (${friendshipsTable.user_id} = ${sessionUserId} AND ${friendshipsTable.friend_id} = ${usersTable.id})
          OR
          (${friendshipsTable.user_id} = ${usersTable.id} AND ${friendshipsTable.friend_id} = ${sessionUserId})
        )
        -- 若你的friendships表有status字段，需追加已确认条件：AND ${friendshipsTable.status} = 'accepted'
      )`,
      // 判断是否存在未处理的好友请求
      hasRequest: sql<boolean>`EXISTS (
        SELECT 1 FROM ${friendRequestsTable}
        WHERE (
          (${friendRequestsTable.sender_id} = ${sessionUserId} AND ${friendRequestsTable.receiver_id} = ${usersTable.id})
          OR
          (${friendRequestsTable.sender_id} = ${usersTable.id} AND ${friendRequestsTable.receiver_id} = ${sessionUserId})
        )
        AND ${friendRequestsTable.status} = 'pending'
      )`,
      // 判断请求发起方（us/them）
      requestFrom: sql<string>`CASE
        WHEN EXISTS (
          SELECT 1 FROM ${friendRequestsTable}
          WHERE ${friendRequestsTable.sender_id} = ${usersTable.id} 
            AND ${friendRequestsTable.receiver_id} = ${sessionUserId} 
            AND ${friendRequestsTable.status} = 'pending'
        ) THEN 'them'
        WHEN EXISTS (
          SELECT 1 FROM ${friendRequestsTable}
          WHERE ${friendRequestsTable.sender_id} = ${sessionUserId} 
            AND ${friendRequestsTable.receiver_id} = ${usersTable.id} 
            AND ${friendRequestsTable.status} = 'pending'
        ) THEN 'us'
        ELSE NULL
      END`,
    })
    .from(friendshipsTable)
    .innerJoin(
      usersTable,
      or(
        and(
          eq(friendshipsTable.user_id, userId),
          eq(friendshipsTable.friend_id, usersTable.id),
          ne(friendshipsTable.friend_id, sessionUserId),
        ),
        and(
          eq(friendshipsTable.user_id, usersTable.id),
          eq(friendshipsTable.friend_id, userId),
          ne(friendshipsTable.user_id, sessionUserId),
        ),
      ),
    );

  // 返回结构与期望一致，每个好友对象包含完整状态字段
  return {
    friendList,
    // 若需要顶层的isFriend/hasRequest/requestFrom（针对sessionUserId和userId的直接关系），可单独添加子查询获取
  };
};

关键说明 #

1. 彻底解决N+1：所有状态判断逻辑都在单次SQL查询中完成，数据库一次性返回包含用户信息和状态的结果集，无需后续循环发起请求。
2. 适配自定义表结构：
   • 如果好友关系和请求都存在friendshipsTable（用status区分accepted/pending），把代码中的friendRequestsTable替换为friendshipsTable并调整条件即可。
3. 性能优化：EXISTS子查询是高效的存在性判断，数据库会在找到匹配记录后立即终止扫描，比全量查询更节省资源。
4. 逻辑对齐原代码：子查询完全复现了checkFriendStatus的判断逻辑，返回的状态字段和之前循环调用的结果完全一致。

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