I’m implementing a permission feature in my service using Prisma (MySQL).
Here are the models:

model SystemFeature {
  id        Int       @id @default(autoincrement()) @map("id") @db.UnsignedSmallInt()
  key       String    @unique @map("key") @db.VarChar(255)
  name      String    @map("name") @db.VarChar(255)
  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime? @updatedAt @map("updated_at")

  roleFeatures SystemRoleFeature[]

  @@map("system_features")
}

model SystemRole {
  id        String    @id @map("id") @db.VarChar(36)
  name      String    @map("name") @db.VarChar(255)
  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime? @updatedAt @map("updated_at")

  roleFeatures SystemRoleFeature[]
  userRoles    UserRole[]

  @@map("system_roles")
}

model SystemRoleFeature {
  roleId    String    @map("role_id") @db.VarChar(36)
  featureId Int       @map("feature_id") @db.UnsignedSmallInt()
  isEnabled Boolean   @map("is_enabled")
  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime? @updatedAt @map("updated_at")

  role    SystemRole    @relation(fields: [roleId], references: [id])
  feature SystemFeature @relation(fields: [featureId], references: [id])

  @@id([roleId, featureId])
  @@map("system_roles_features")
}

• SystemFeature: Represents a feature in the system, such as TASK_VEW, TASK_UPDATE, or TASK_REMOVE;
• SystemRole: Represents a role in the system, such as ADMIN or READ_ONLY;
• SystemRoleFeature: Gives the SystemRole permission to access SystemFeature;

In my service, I’m trying to get all features associated with a role, with isEnabled as true;

I tried the following code:

// Option 1
return this.prisma.systemFeature.findMany({
  where: {
    roleFeatures: {
      some: {
        roleId: user.role.systemRoleId,
        isEnabled: true,
      },
    },
  },
});

// Option 2
const systemRoleFeatures = await this.prisma.systemRoleFeature.findMany({
  where: {
    roleId: user.role.systemRoleId,
    isEnabled: true,
  },
  include: { feature: true },
});
return systemRoleFeatures.map((systemRoleFeature) => systemRoleFeature.feature);

Here are the generated queries:

// Option 1
SELECT
    `t1`.`id`,
    `t1`.`key`,
    `t1`.`name`,
    `t1`.`created_at` AS `createdAt`,
    `t1`.`updated_at` AS `updatedAt`
FROM
    `gestorcondo`.`system_features` AS `t1`
WHERE
    (`t1`.`id`) IN (
    SELECT
        `t1`.`feature_id`
    FROM
        `gestorcondo`.`system_roles_features` AS `t1`
    WHERE
        (`t1`.`role_id` = 'CONSULTA'
            AND `t1`.`is_enabled` = 1
            AND `t1`.`feature_id` IS NOT NULL));

// Option 2
SELECT
    `t1`.`role_id` AS `roleId`,
    `t1`.`feature_id` AS `featureId`,
    `t1`.`is_enabled` AS `isEnabled`,
    `t1`.`created_at` AS `createdAt`,
    `t1`.`updated_at` AS `updatedAt`,
    (
    SELECT
        JSON_OBJECT('id', `t2`.`id`, 'key', `t2`.`key`, 'name', `t2`.`name`, 'createdAt', `t2`.`created_at`, 'updatedAt', `t2`.`updated_at`)
    FROM
        `gestorcondo`.`system_features` AS `t2`
    WHERE
        `t1`.`feature_id` = `t2`.`id`
    LIMIT 1) AS `feature`
FROM
    `gestorcondo`.`system_roles_features` AS `t1`
WHERE
    (`t1`.`role_id` = 'CONSULTA'
        AND `t1`.`is_enabled` = 1);

As you can see, none of the options are using JOIN. I confirmed that I’m using relationJoins and also tried adding relationLoadStrategy: 'join' directly to the query. Why is Prisma not executing something like the following?

SELECT sf.*
FROM system_features sf
JOIN system_roles_features srf ON srf.feature_id = sf.id
WHERE srf.role_id = 'CONSULTA'
AND srf.is_enabled = 1;

How to Prisma execute queries with JOINs in cases like this?