Kysely•2y ago

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•2y 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')
      ])
  )

AlexErrantOP•2y ago

Howdy 🙋‍♂️ Unfortunately PlanetScale/Vitess doesn't support CTEs. Thanks anyways!

Igal•2y 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()

AlexErrantOP•2y 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•2y 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•2y 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•2y 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•2y 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.

Gaming

Programming

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