How to make a top level SELECT that isn't from a table?

I'm trying to combine two queries, SELECT COUNT(*) FROM A and SELECT COUNT(*) FROM B. SELECT (SELECT COUNT(*) FROM A), (SELECT COUNT(*) FROM B) does what I want. How can I convert this to Kysely? My real code also returns two scalars, so I could theoretically use a UNION ALL - however I'm not sure if the return order is guaranteed in MySQL. I'm open to other solutions that don't have a top level SELECT.
8 Replies
Igal
Igal2y ago
Hey 👋🏻 There are various ways to achieve this
db.with('a_count', (qc) => qc.selectFrom('A')
.select((eb) => eb.fn.countAll().as('a_count')))
.with('b_count', (qc) => qc.selectFrom('B')
.select((eb) => eb.fn.countAll().as('b_count')))
.selectFrom(['a_count', 'b_count'])
.selectAll()
db.with('a_count', (qc) => qc.selectFrom('A')
.select((eb) => eb.fn.countAll().as('a_count')))
.with('b_count', (qc) => qc.selectFrom('B')
.select((eb) => eb.fn.countAll().as('b_count')))
.selectFrom(['a_count', 'b_count'])
.selectAll()
db.selectFrom('A')
.select([
sql.literal('A').as('source'),
(eb) => eb.fn.countAll().as('count')
])
.unionAll(
db.selectFrom('B')
.select([
sql.literal('B').as('source'),
(eb) => eb.fn.countAll().as('count')
])
)
db.selectFrom('A')
.select([
sql.literal('A').as('source'),
(eb) => eb.fn.countAll().as('count')
])
.unionAll(
db.selectFrom('B')
.select([
sql.literal('B').as('source'),
(eb) => eb.fn.countAll().as('count')
])
)
AlexErrant
AlexErrant2y ago
Howdy 🙋‍♂️ Unfortunately PlanetScale/Vitess doesn't support CTEs. Thanks anyways!
Igal
Igal2y ago
Damn, well the 2nd option should do the trick, and you won't have to worry about order guarantees. Alternative to CTEs:
db.selectFrom([
db.selectFrom('A').select((eb) => eb.fn.countAll().as('a_count')).as('A'),
db.selectFrom('B').select((eb) => eb.fn.countAll().as('b_count')).as('B'),
]).selectAll()
db.selectFrom([
db.selectFrom('A').select((eb) => eb.fn.countAll().as('a_count')).as('A'),
db.selectFrom('B').select((eb) => eb.fn.countAll().as('b_count')).as('B'),
]).selectAll()
AlexErrant
AlexErrant2y ago
ohh I see what you're doing with sql.literal. I'm going with your third answer because it seems cleaner (and slightly more typesafe), but your second one also works. Greatly appreciate your multiple answers - thanks!
Kristian Notari
Kristian Notari16mo ago
@Igal shouldn't be possible to just do a selectFrom without passing any parameter or just select method directly? I'm trying to do a: SELECT EXISTS (subquery) query
Igal
Igal16mo ago
If we add this, it would be very confusing for newcomers, "why am I using db.select(db.exists(db.selectFrom('person')...)) and can't chain it with from, where, etc". You probably can do it today with:
sql`select exists(${db.selectFrom('person').selectAll().where('id', '=', personId)})`.execute(db)
sql`select exists(${db.selectFrom('person').selectAll().where('id', '=', personId)})`.execute(db)
Kristian Notari
Kristian Notari16mo ago
yeah I obviusly can, but the from clause for a select query is not always required, why imposing one in the selectFrom() ? I mean, I can guess why, but do you think it makes sense to also accept no parameters there?
Igal
Igal16mo ago
It doesn't make sense, you're doing selectFrom but from doesn't end up in the query. we could add a db.select((eb) => eb.exists(eb.selectFrom('person').selectAll().where('id', '=', personId)) method, can only execute or compile.
Want results from more Discord servers?
Add your server