能否在MikroOrm中使用公共表表达式（CTE）？含非常规方法

MikroORM目前没有提供专门的CTE API，但可以通过以下几种方式实现，包括非常规变通方案：

常规实现方式 #

直接执行原生CTE查询 #

利用EntityManager.nativeQuery()直接编写包含CTE的原生SQL：

const cteSql = `
  WITH active_users AS (
    SELECT id, name, email FROM users WHERE is_active = true
  )
  SELECT * FROM active_users WHERE email LIKE '%@example.com'
`;
const result = await em.nativeQuery(cteSql).execute();

结合QueryBuilder插入CTE片段 #

如果需要部分借助MikroORM的查询构建能力，可通过raw()方法注入CTE语句：

const qb = em.createQueryBuilder(User);
qb.raw(`WITH active_users AS (SELECT id, name FROM users WHERE is_active = true)`)
  .select('au.id', 'au.name')
  .from('active_users', 'au');
const users = await qb.execute();

非常规变通方案 #

封装CTE为数据库存储过程 #

在数据库中创建包含CTE逻辑的存储过程，通过MikroORM调用该存储过程：

// 假设已创建存储过程GetFilteredActiveUsers
const users = await em.nativeQuery('CALL GetFilteredActiveUsers(\'%@example.com\')').execute();

实体类中封装CTE查询逻辑 #

在实体类中定义静态方法，将CTE查询逻辑封装起来，便于复用：

@Entity()
export class User {
  @PrimaryKey()
  id!: number;

  @Property()
  name!: string;

  @Property()
  email!: string;

  @Property()
  is_active!: boolean;

  static async getFilteredActiveUsers(em: EntityManager, emailPattern: string) {
    const cteSql = `
      WITH active_users AS (
        SELECT id, name, email FROM users WHERE is_active = true
      )
      SELECT * FROM active_users WHERE email LIKE ?
    `;
    return em.nativeQuery(cteSql, User).setParameters([emailPattern]).getResult();
  }
}

// 使用示例
const filteredUsers = await User.getFilteredActiveUsers(em, '%@example.com');

目前MikroORM官方尚未支持CTE的专用API，上述方案均基于原生SQL能力实现，可满足绝大多数CTE使用场景。

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