Achieve Prisma-like nested selects
Hi. Is it possible to achieve Prisma-like nested selects with Kysely? I have the following code and it has the type below in Prisma, but I'm not sure how to achieve the same in Kysely without just doing an additional
select * from CommitteeCountry where id in (select id from Committee)
or something along those lines and then mapping the results in JS
Solution:Jump to solution
Relations | Kysely
Kysely IS NOT an ORM. Kysely DOES NOT have the concept of relations.
8 Replies
i mean i assume it doesnt exist in traditional sql since you don't have arrays there so i wouldn't be surprised if i had to do some clientside mapping to get my desired result
Solution
Hey 👋
Have you read this? https://kysely.dev/docs/recipes/relations
Relations | Kysely
Kysely IS NOT an ORM. Kysely DOES NOT have the concept of relations.
tldr: we offer PostgreSQL & MySQL helpers for that use case
omg thats amazing
kysely is actually the best ngl thank you devs ❤️
Unknown User•12mo ago
Message Not Public
Sign In & Join Server To View
Yep, Kysely can't know if the selected expression exists or not. Therefore the type must be
T | null
. For example, try adding where('site.id', '=', 'lol not a site id')
to the subquery. Kysely can't know the id
'lol not a site id'
doesn't exist in the db.
You can chain
to the query for now. We're going to add a nicer helper
soon that will make these cases easier.Unknown User•12mo ago
Message Not Public
Sign In & Join Server To View
Kysely doesn't know anything about your prisma schema.
And kysely doesn't understand anything about relations. All it sees is arbitrary expressions. All kysely knows about your schema is the types. And obviously that's the only thing it can use since we are talking about how things are typed.
The types contain zero knowledge about relations, since kysely doesn't have the concept of a relation. All it knows about are columns and whether they are nullable or not.
In theory, Kysely could detect that the only comparison in the subquery is between two non-null columns and then remove the null from the output type, but doing that on the type-level would be insane.
The only thing kysely has to work with here is whether a column in a comparison is nullable or not. Column's nullability doesn't mean it's a foreign key in a relation or something.
Also, as soon as you add any other
where
statements, we couldn't know that anymore.
And if the table is empty, you'd still get a null. By the way, you are using a left join... That'd indicate the relation is in fact nullable.