Kysely•2y ago

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

const data = await prisma.Committee.findMany({
    include: {
        countries: true,
    },
})

{
  id: number
  ...

  countries: {
    id: number
    committeeId: number
    ...
  }[]
}

const data = await prisma.Committee.findMany({
    include: {
        countries: true,
    },
})

{
  id: number
  ...

  countries: {
    id: number
    committeeId: number
    ...
  }[]
}

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.

Jump to solution

8 Replies

oof2win2OP•2y ago

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

Igal•2y ago

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.

Igal•2y ago

tldr: we offer PostgreSQL & MySQL helpers for that use case

oof2win2OP•2y ago

omg thats amazing kysely is actually the best ngl thank you devs ❤️

Unknown User•14mo ago

Message Not Public

koskimas•14mo ago

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

.$narrowType<{ site: Selectable<Site> }>()

.$narrowType<{ site: Selectable<Site> }>()

to the query for now. We're going to add a nicer helper

.$narrowNotNull<'site'>()

.$narrowNotNull<'site'>()

soon that will make these cases easier.

Unknown User•14mo ago

Message Not Public

koskimas•14mo ago

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.

Gaming

Programming

Achieve Prisma-like nested selects