Kysely•3y 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

koskimas•7/4/23, 9:04 AM

You can always use raw sql

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

mikeOP•7/4/23, 9:09 AM

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

koskimas•7/4/23, 9:10 AM

If the table alias is dynamic, then no.

koskimas•7/4/23, 9:10 AM

There's no way to statically type something that's determined during runtime

mikeOP•7/4/23, 9:13 AM

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'...

mikeOP•7/4/23, 9:15 AM

I have my custom sql framework where I can do something like this:

alias<T>(aliasName, columnName = keyof T)

output:

aliasName.columnName

mikeOP•7/4/23, 9:15 AM

is there something similar to this in kysely?

koskimas•7/4/23, 9:19 AM

Something like this?

Solution

koskimas•7/4/23, 9:20 AM

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

mikeOP•7/4/23, 9:20 AM

hm, nice hacking...

koskimas•7/4/23, 9:20 AM

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

mikeOP•7/4/23, 9:21 AM

I know - by "hacking" I mean adding the alias on reference to the table on-the-go

mikeOP•7/4/23, 9:21 AM

it makes sense to me. thanks

mikeOP•7/4/23, 9:48 AM

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

Mmike One more question - how can I use coalesce in this scenario. I did several attem...

decho•7/4/23, 10:27 AM

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

decho•7/4/23, 10:28 AM

but it's not very type-safe

Igal•7/4/23, 11:08 AM

mikeOP•7/4/23, 1:08 PM

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

koskimas•7/4/23, 1:38 PM

creates a parameter. 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.

koskimas•7/4/23, 1:39 PM

I think postgres function does infer the type from earlier arguments so you don't need to use .

koskimas•7/4/23, 1:40 PM

Kysely treats both and equally until code generation.