如何在Drizzle ORM中避免SQL扇出并解决关联查询重复数据问题

你的问题确实是SQL多表关联导致的笛卡尔积：当一个账单关联2个用户和1笔费用时，关联查询会生成2条记录（每个用户对应同一条费用），用json_agg聚合时，这条费用会被重复统计，最终数组里出现两次相同的费用。

修正方案：用子查询分别聚合用户和费用 #

避免多表关联产生笛卡尔积的最稳妥方式是，针对每个账单单独查询其关联的用户和费用，通过子查询完成聚合：

const bill = await db
  .select({
    id: billsTable.id,
    createdBy: billsTable.createdBy,
    createdAt: billsTable.createdAt,
    name: billsTable.name,
    balance: billsTable.balance,
    // 子查询获取当前账单的关联用户
    users: sql<User[]>`(
      SELECT json_agg(json_build_object(
        'id', u.id, 
        'email', u.email, 
        'name', u.name, 
        'image', u.image
      ))
      FROM users_to_bills utb
      JOIN users u ON utb.user_id = u.id
      WHERE utb.bill_id = ${billsTable.id}
    )`,
    // 子查询获取当前账单的关联费用
    expenses: sql<Expense[]>`(
      SELECT json_agg(json_build_object(
        'id', e.id, 
        'amountSpent', e.amount_spent, 
        'createdAt', e.created_at, 
        'createdBy', e.created_by, 
        'note', e.note
      ))
      FROM expenses e
      WHERE e.bill_id = ${billsTable.id}
    )`,
  })
  .from(billsTable)
  .where(eq(billsTable.id, params.billId));

如果你更倾向于用Drizzle的链式API而非原生SQL字符串，可以这样写子查询：

const bill = await db
  .select({
    id: billsTable.id,
    createdBy: billsTable.createdBy,
    createdAt: billsTable.createdAt,
    name: billsTable.name,
    balance: billsTable.balance,
    users: sql<User[]>`(
      ${db
        .select({
          id: usersTable.id,
          email: usersTable.email,
          name: usersTable.name,
          image: usersTable.image,
        })
        .from(usersTable)
        .innerJoin(billsToUsers, eq(usersTable.id, billsToUsers.userId))
        .where(eq(billsToUsers.billId, billsTable.id))
        .then(sub => sql`json_agg(${sub})`)}
    )`,
    expenses: sql<Expense[]>`(
      ${db
        .select({
          id: expensesTable.id,
          amountSpent: expensesTable.amountSpent,
          createdAt: expensesTable.createdAt,
          createdBy: expensesTable.createdBy,
          note: expensesTable.note,
        })
        .from(expensesTable)
        .where(eq(expensesTable.billId, billsTable.id))
        .then(sub => sql`json_agg(${sub})`)}
    )`,
  })
  .from(billsTable)
  .where(eq(billsTable.id, params.billId));

另一种快速修复：在聚合中使用DISTINCT #

如果不想改结构，也可以在json_agg里加上DISTINCT，强制去重：

expenses: sql<Expense[]>`json_agg(DISTINCT json_build_object(
  'id', ${expensesTable.id}, 
  'amountSpent', ${expensesTable.amountSpent},
  'createdAt', ${expensesTable.createdAt}, 
  'createdBy', ${expensesTable.createdBy},
  'note', ${expensesTable.note}
)) FILTER (WHERE ${expensesTable.id} IS NOT NULL)`,

⚠️ 注意：这种方式依赖PostgreSQL对JSON对象的比较逻辑，只有当两个JSON对象的键顺序、值完全一致时才会被视为重复。如果你的费用数据结构有变动，可能会失效，子查询的方式更可靠。

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