如何在MikroORM中执行SQL的UNION命令？

我希望在MikroORM中使用PostgreSQL的union语法，但框架没有提供内置的qb.union()这类方法，因此考虑两种实现方式：通过qb.raw()执行原生SQL，或是借助Knex构建查询。

我的需求是：根据指定条件从表中获取数据，同时无论条件是否匹配，都要返回至少两行ID最大的数据，对应的原生SQL如下：

(
  select * from sch.tbl
  -- where  -- 可插入条件，例如 `id > 10`
  order by id desc
) union (
  select * from sch.tbl order by id desc limit 2
) order by id asc;

尝试过程中的问题 #

1. Knex构建查询但无法获取结果
   我尝试用Knex构建查询，但不知道如何获取最终结果：

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

2. 原生SQL占位符使用错误
   使用qb.raw()时，生成的查询不符合预期：

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'])

qb.getQuery()输出的是select "s0".* from "sch"."tbl" as "s0"，并非目标SQL。

后来改用Knex的raw方法时，发现用?作为标识符占位符会报错：

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

// 执行失败的代码
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: (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"

直接写死标识符的代码可以正常运行，但无法使用绑定参数：

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'])

最终解决方案 #

查阅Knex文档后发现，?用于绑定值，??用于绑定标识符。同时找到了通过Knex构建查询，并借助MikroORM映射为带TypeScript类型实体的完整方案：

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 // 传递true表示使用UNION ALL（可选，根据需求调整）
  )
  .orderBy('start_time', 'asc')

// 获取无类型的原始结果
const res = await this.orm.em.getConnection().execute(query)
// 将结果映射为带TypeScript类型的实体数组
const entities = res.map(e => this.orm.em.map(StatusInterval, e))

内容的提问来源于stack exchange，提问作者tukusejssirs