Kysely•2y ago

How to reference the parent query when creating a subquery itself?

What if I want to use kysely to create a subquery with a literal reference only and use it in sequelize ORM as literal? // Here I want to generate the subquery with a reference to non-existing parent const kyselySubquery =

select
    id
from
    pricing as latest
where
    latest.product_id = parent.product_id // parent does not exists in this context...!
order by
    latest.date desc
limit 1

select
    id
from
    pricing as latest
where
    latest.product_id = parent.product_id // parent does not exists in this context...!
order by
    latest.date desc
limit 1

// I plan to use it in my ORM as a subquery literal select * from pricing as parent where id in /*kyselySubquery */

Solution:

https://kyse.link/?p=s&i=eonkCC4XRvE93k3Bq7QN

Jump to solution

13 Replies

koskimas•2y ago

You can always use raw sql

where('latest.product_id', '=', sql`parent.product_id`)

where('latest.product_id', '=', sql`parent.product_id`)

If the name of the parent table needs to get injected dynamically you can use the sql.id function:

where('latest.product_id', '=', sql.id(table, 'product_id'))

where('latest.product_id', '=', sql.id(table, 'product_id'))

mikeOP•2y ago

the first one will loose all the typing benefits, right? the second one - is it possible define the 'parent' alias?

koskimas•2y ago

If the table alias is dynamic, then no. There's no way to statically type something that's determined during runtime

mikeOP•2y ago

it's clear. It can be defined statically without any problem - 'parent' in this example but how the sql.id would help me with the typing? I am looking for a way how to get the 'product_id' from the pricing interface and still name the alias 'parent'... I have my custom sql framework where I can do something like this: alias<T>(aliasName, columnName = keyof T) output: aliasName.columnName is there something similar to this in kysely?

koskimas•2y ago

Something like this?

Solution

koskimas•2y ago

https://kyse.link/?p=s&i=eonkCC4XRvE93k3Bq7QN

mikeOP•2y ago

hm, nice hacking... 🙂

koskimas•2y ago

Well, not really hacking.. That's how the tables are expressed in kysely

mikeOP•2y ago

I know - by "hacking" I mean adding the alias on reference to the table on-the-go it makes sense to me. thanks One more question - how can I use coalesce in this scenario. I did several attempts but always hit the dead-end. https://kyse.link/?p=s&i=fuIRG3ZjAUr4gZPJnviT

decho•2y ago

Hey, no idea if this is the best solution, but it seems to work:

    .where(({ cmpr, ref, val }) =>
      cmpr(
        sql`coalesce(${ref('parent.manager_id')}, ${val(-1)})`,
        '=',
        sql`coalesce(${ref('parent.manager_id')}, ${val(-1)})`
      )
    )

    .where(({ cmpr, ref, val }) =>
      cmpr(
        sql`coalesce(${ref('parent.manager_id')}, ${val(-1)})`,
        '=',
        sql`coalesce(${ref('parent.manager_id')}, ${val(-1)})`
      )
    )

but it's not very type-safe

Igal•2y ago

.where(({ cmpr, fn }) =>
        cmpr(
          fn.coalesce("parent.manager_id", sql.lit(-1)),
          "=",
          fn.coalesce("parent.manager_id", sql.lit(-1)),
        ),
      )

.where(({ cmpr, fn }) =>
        cmpr(
          fn.coalesce("parent.manager_id", sql.lit(-1)),
          "=",
          fn.coalesce("parent.manager_id", sql.lit(-1)),
        ),
      )

mikeOP•2y ago

yes, it works as expected. any guide when to use val() and when sql.lit()...?

koskimas•2y ago

val creates a parameter. lit creates a literal value that gets inlined in the SQL. Parameters don't contain a data type and they are usually assumed to be strings in the db unless the type can be inferred from the context. For example in a binary expression, the parameter's type is inferred by the other operand by the db engine. If there's nothing to infer the type from, you get a string. In these cases you need to use a cast or a literal value. I think postgres coalesce function does infer the type from earlier arguments so you don't need to use lit. Kysely treats both lit and val equally until code generation.

Gaming

Programming

How to reference the parent query when creating a subquery itself?