Querying Many-to-Many relations in Prisma

I have following schema.prisma generated from existing db structure:

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")
}

I want to query all users related to an account with specific accountUid

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

This produces the following query:

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)

This query contains EXISTS with a subquery which is not the best for performance I'm afraid it may get performance issues when users table grow

More logical query for me should be to join all three tables for filtering:

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

Is it possible to hint Prisma to use JOIN instead of EXISTS here?

Use a raw SQL query directly if you want more control over the result query and want to guarantee using a JOIN. It's a common concern when dealing with ORMs and their generated query, especially with many-to-many relationships.

As for better performance of JOIN over EXISTS, it's a nuanced question, sometimes it can be more of an outdated stereotype. Modern database optimizers (like PostgreSQL, MySQL, Oracle) are incredibly sophisticated. They're very good at optimizing EXISTS clauses, often transforming them internally into a JOIN if that's more efficient for the given query plan.

Your generated EXISTS subquery is a correlated subquery because u.id = u2a.user_id links it back to the outer users table. Database optimizers are particularly adept at optimizing correlated subqueries.

Indexes matter more. The performance of both your EXISTS query and your proposed JOIN query will be far more dependent on proper indexing than on the fundamental choice between EXISTS and JOIN.

For this EXISTS query, ensuring you have indexes on user2acc.user_id, user2acc.account_id, and accounts.account_uid is crucial.

Data distribution and selectivity are also very important. The size of your tables, especially potential growing of the user table user2acc and the selectivity of your WHERE clause (a.account_uid = :accountUid) will also heavily influence performance. If account_uid is highly selective (only a few users per account), both queries should perform well with good indexes.

Check the EXPLAIN output for your specific database and data to truly understand the performance characteristics and the best strategy in your case.