如何让Prisma查询多对多关系时使用JOIN而非EXISTS？

现有数据库Schema（Prisma定义） #

model accounts {
  account_id  Int        @id(map: "accounts_pkey1") @default(autoincrement())
  account_uid String     @unique(map: "accounts_uid_pk") @db.VarChar(9)
  user2acc    user2acc[]
}

model users {
  id       Int        @id @default(autoincrement())
  name     String?    @db.VarChar(255)
  email    String     @unique
  user2acc user2acc[]
}

model user2acc {
  id         Int       @id(map: "user2acc_pk") @default(autoincrement())
  user_id    Int?
  account_id Int?  
  role_code  String    @default("user") @db.VarChar(10)
  accounts   accounts? @relation(fields: [account_id], references: [account_id], onDelete: NoAction, onUpdate: NoAction, map: "user2acc_accounts_account_id_fk")
  users      users?    @relation(fields: [user_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "user2acc_users_id_fk")

  @@unique([user_id, account_id], map: "user2acc_user_id_account_id_uindex")
}

当前查询实现及生成的SQL #

我需要查询与特定accountUid关联的所有用户，当前使用的Prisma代码如下：

prisma.users.findMany(
    {where: {
        user2acc: {
            some: {
                accounts: {account_uid: accountUid}
            }
        }
    }},
)

这段代码生成的SQL使用了EXISTS子查询：

SELECT u.id,
       u.name,
       u.email
FROM users u
WHERE EXISTS(SELECT u2a.user_id
             FROM user2acc u2a
                      LEFT JOIN accounts a ON a.account_id = u2a.account_id
             WHERE a.account_uid = :accountUid
               AND u.id = u2a.user_id)

期望的SQL写法 #

我认为更高效的写法是通过JOIN关联三张表过滤数据：

SELECT u.id,
       u.name,
       u.email
FROM users u
JOIN user2acc ua ON u.id = ua.user_id
JOIN accounts a ON a.account_id = ua.account_id
WHERE a.account_uid = :accountUid

问题与解决方案 #

是否可以让Prisma生成JOIN而非EXISTS的查询？ #

可以通过调整查询逻辑，从中间关联表user2acc出发发起查询，引导Prisma生成JOIN语句：

// 查询关联数据并提取用户集合
const userRelations = await prisma.user2acc.findMany({
  where: {
    accounts: { account_uid: accountUid }
  },
  include: {
    users: true
  },
  distinct: ['user_id'] // 避免同一用户重复返回
});

// 提取最终的用户数据
const users = userRelations.map(item => item.users).filter(Boolean);

如果希望直接获取结构化的用户数据，也可以用select指定需要的字段：

const users = await prisma.user2acc.findMany({
  where: {
    accounts: { account_uid: accountUid }
  },
  select: {
    users: {
      select: {
        id: true,
        name: true,
        email: true
      }
    }
  },
  distinct: ['user_id']
}).then(res => res.map(item => item.users).filter(Boolean));

原理说明 #

当从中间关联表user2acc发起查询并关联users和accounts时，Prisma会优先生成JOIN语句——因为此时查询逻辑是直接遍历关联关系，而非检查用户是否存在关联记录，自然会采用JOIN的写法。

性能优化补充 #

• 确保user2acc.user_id、user2acc.account_id、accounts.account_uid字段都已创建索引，这能让JOIN查询的性能最大化。
• 使用distinct是为了和原EXISTS查询的去重效果保持一致，避免同一用户因关联多个角色/账户重复出现。

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