How to run `union` SQL command in MikroORM

Question

Also asked at GitHub.

---

I’d like to use union in MikroORM. There doesn’t seem to be a built-in way (like qb.union(), therefore I think I either need to run a raw SQL command (via qb.raw()) or use Knex to build the command.

For example, I want to get some rows from a table based on some condition, however, at any case, return at least two rows with the greatest ID.

(
  select * from sch.tbl
  -- where  -- insert some condition, e.g. `id > 10`
  order by id desc
) union (
  select * from sch.tbl order by id desc limit 2
) order by id asc;

which I presume can be accomplished with Knex like this:

this.orm.em.getKnex()
  .union(qb => qb
    .select('*')
    .withSchema('sch')
    .from('tbl')
    .where('id', '>', 10)
    .orderBy('id', 'DESC')
    .limit(10)
  )
  .union(qb => qb
    .select('*')
    .withSchema('sch')
    .from('tbl')
    .orderBy('id', 'DESC')
    .limit(2)
  )
  .orderBy('id', 'ASC')

However, I have no idea how to get the result.

When I run the following:

qb.raw('(select * from ?.? where id > ? order by id desc limit 10) union (select * from ?.? order by id desc limit 2) order by id asc', ['sch', 'tbl', 10, 'sch', 'tbl'])

it builds a query (output of qb.getQuery()) select "s0".* from "sch"."tbl" as "s0", which is not what I wanted.

Thanks for your help!

Update

I can use the following, however, when I use ? placeholders, it fails as it replaces them with $1 (1 is incremented). However, it works without the placeholders as expected.

const knex = this.orm.em.getKnex()

// This fails
const result = await knex.raw('(select * from ?.? where id > ? order by id desc limit 10) union (select * from ?.? order by id desc limit 2) order by id asc', ['sch', 'tbl', 10, 'sch', 'tbl'])

// Error
error: (select * from $1.$2 where id > $3 order by id desc limit 10) union (select * from $4.$5 order by id desc limit 2) order by id asc - syntax error at or near "$1"

// This works
const x = await knex.raw('(select * from sch.tbl where id > 10 order by id desc limit 10) union (select * from sch.tbl order by id desc limit 2) order by id asc', ['sch', 'tbl', 10, 'sch', 'tbl'])

While I’d prefer some syntactic sugar commands (either in MikroORM or Knex), raw SQL commands are a good workaround, however, I want to use the placeholders (bindings). How can I accomplish that?

---

Update 2

Okay, I missed that note in the Knex docs that ? is interpreted as value and ?? as identifier.

Furthermore, I’ve found a solution using Knex (albeit I use it from MikroORM):

const knex = this.orm.em.getKnex()

const xKnexRaw = knex
  .withSchema('sch')
  .select('*')
  .from('tbl')
  .where('id', '>', 10)
  .orderBy('start_time', 'desc')
  .limit(10)
  .union(
    qb => qb
      .select('*')
      .withSchema('sch')
      .from('tbl')
      .orderBy('id', 'desc')
      .limit(2),
    true
  )
  .orderBy('start_time', 'asc')

// `res` is a result (array of row) without TypeScript types
const res = await this.orm.em.getConnection().execute(query)
// `entities` is a result (array of entities) with mapped TypeScript types
const entities = res.map(e => this.orm.em.map(StatusInterval, e))

Answer 1

This is a solution using Knex (albeit I use it from MikroORM):

const knex = this.orm.em.getKnex()

const query = knex
  .withSchema('sch')
  .select('*')
  .from('tbl')
  .where('id', '>', 10)
  .orderBy('start_time', 'desc')
  .limit(10)
  .union(
    qb => qb
      .select('*')
      .withSchema('sch')
      .from('tbl')
      .orderBy('id', 'desc')
      .limit(2),
    true
  )
  .orderBy('start_time', 'asc')

// `res` is a result (array of row) without TypeScript types
const res = await this.orm.em.getConnection().execute(query)
// `entities` is a result (array of entities) with mapped TypeScript types
const entities = res.map(e => this.orm.em.map(StatusInterval, e))