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 =
// I plan to use it in my ORM as a subquery literal
select
*
from
pricing as parent
where
id in /*kyselySubquery */
13 Replies
You can always use raw sql
If the name of the parent table needs to get injected dynamically you can use the
sql.id function:
the first one will loose all the typing benefits, right? the second one - is it possible define the 'parent' alias?
If the table alias is dynamic, then no.
There's no way to statically type something that's determined during runtime
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?
Something like this?
Solution
hm, nice hacking... 🙂
Well, not really hacking.. That's how the tables are expressed in kysely
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
Hey, no idea if this is the best solution, but it seems to work:
but it's not very type-safe
yes, it works as expected. any guide when to use val() and when sql.lit()...?
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.