P
Prisma•2mo ago
DeathlyNocturnal

Prisma isn't doing joins.

Hi, I have just setup the relation strategy as we would like the ORM to use join queries. But I have noticed it just won't do a join, even after setting the preview feature and then trying the relationLoadStrategy. Here is a minifed version: 
model rules {
    id          String   @id @db.Char(16)
    name        String   @db.VarChar(255)
    object_id   String   @db.Char(16)
    object      objects  @relation(fields: [object_id], references: [id])
}

model objects {
    id            String    @id @db.Char(16)
    original_id   String?   @db.Char(16)
    rules         rules[]
}
model rules {
    id          String   @id @db.Char(16)
    name        String   @db.VarChar(255)
    object_id   String   @db.Char(16)
    object      objects  @relation(fields: [object_id], references: [id])
}

model objects {
    id            String    @id @db.Char(16)
    original_id   String?   @db.Char(16)
    rules         rules[]
}
And I am doing: 
const rule = await prisma.rules.findFirst({
    relationLoadStrategy: 'join',
    select: {
        name: true,
        object: {
            select: {
                name: true,
            },
        },
    },
});
const rule = await prisma.rules.findFirst({
    relationLoadStrategy: 'join',
    select: {
        name: true,
        object: {
            select: {
                name: true,
            },
        },
    },
});
but I am still seeing this: 
prisma:query SELECT `t0`.`id`, `t0`.`name`, `t0`.`object_id`, (SELECT JSON_OBJECT('name', `t1`.`name`) FROM `createdb`.`objects` AS `t1` WHERE `t0`.`object_id` = `t1`.`id` LIMIT ?) AS `object` FROM `createdb`.`rules` AS `t0` LIMIT ?
Query: SELECT `t0`.`id`, `t0`.`name`, `t0`.`object_id`, (SELECT JSON_OBJECT('name', `t1`.`name`) FROM `createdb`.`objects` AS `t1` WHERE `t0`.`object_id` = `t1`.`id` LIMIT ?) AS `object` FROM `createdb`.`rules` AS `t0` LIMIT ?
Params: [1,1]
prisma:query SELECT `t0`.`id`, `t0`.`name`, `t0`.`object_id`, (SELECT JSON_OBJECT('name', `t1`.`name`) FROM `createdb`.`objects` AS `t1` WHERE `t0`.`object_id` = `t1`.`id` LIMIT ?) AS `object` FROM `createdb`.`rules` AS `t0` LIMIT ?
Query: SELECT `t0`.`id`, `t0`.`name`, `t0`.`object_id`, (SELECT JSON_OBJECT('name', `t1`.`name`) FROM `createdb`.`objects` AS `t1` WHERE `t0`.`object_id` = `t1`.`id` LIMIT ?) AS `object` FROM `createdb`.`rules` AS `t0` LIMIT ?
Params: [1,1]
What is happening? why is it ignoring what I ask it?
2 Replies
Prisma AI Help
Prisma AI Help•2mo ago
Skipping coffee for instant knowledge! Let's brew that AI answer now. You're seeing the expected behavior for Prisma's relationLoadStrategy: 'join' on MySQL. When you enable the relationJoins preview feature and use relationLoadStrategy: 'join', Prisma does not use a traditional SQL JOIN clause for MySQL. Instead, it uses correlated subqueries to fetch related data in a single query. This is why your query output shows a subquery with JSON_OBJECT rather than a JOIN statement. This is by design:
- PostgreSQL uses LATERAL JOIN and JSON aggregation. - MySQL uses correlated subqueries with JSON aggregation to achieve similar results, not a JOIN keyword in the SQL output. From the docs:
The join (default) strategy uses a database-level LATERAL JOIN (PostgreSQL) or correlated subqueries (MySQL) and fetches all data with a single query to the database.
Docs: relationLoadStrategy (Preview) So, Prisma is not ignoring your instructions—it's just that for MySQL, the "join" strategy is implemented using correlated subqueries, not explicit SQL JOIN clauses. The result is still a single query fetching all the data, which is the intended optimization. If you need a traditional SQL JOIN for some reason, you would need to use a raw query. Otherwise, Prisma's approach is working as designed for MySQL with the preview feature enabled Docs: Nested reads.
Nurul
Nurul•2mo ago
Was the bot response helpful?

Did you find this page helpful?