如何使用MikroORM查询构建器实现指定自连接SQL查询

需要用MikroORM查询构建器实现以下SQL查询：

SELECT * FROM (
    SELECT * FROM `gps_log`                                           -- Same table
    WHERE log_time >= DATE_SUB(NOW(), INTERVAL 232324324 MINUTE)
) as g
JOIN (SELECT MAX(gps_log_id) as gps_id, consignment_consignment_id
      FROM `gps_log`                                                   -- Same table
      GROUP BY consignment_consignment_id
     ) AS max ON max.gps_id = g.gps_log_id 

目前尝试通过获取Knex实例执行原生SQL，但觉得不够简洁且未成功，代码如下：

const qb = this.em.createQueryBuilder(GpsLog);
const knex = qb.getKnexQuery(); // instance of Knex' QueryBuilder

knex.select("*")
    .fromRaw(
        "(SELECT * FROM `gps_log` WHERE log_time >= DATE_SUB(NOW(), INTERVAL 232324324 MINUTE)) as g"
    )
    .joinRaw(
        "(SELECT MAX(gps_log_id) as gps_id, consignment_consignment_id FROM `gps_log` GROUP BY consignment_consignment_id) AS max ON max.gps_id = g.gps_log_id"
    )

const res = await this.em.getConnection().execute(knex);
const result = res.map(a => this.em.map(GpsLog, a));

return result;

实体类定义：

@Entity()
export class GpsLog {

    /* SQL properties */
    @PrimaryKey({ hidden: true })
    gpsLogId!: number;

    @Property({ type: types.double })
    long!: string;
    
    @Property({ type: types.double })
    lat!: string;

    @Property()
    logTime: Date;


    /* JSON Formatter */
    @Property({ persist: false })
    get self() {
        return `/consgts/${this.consignment}/logs/${this.gpsLogId}`;
    }

    @Property({ persist: false })
    get consgt() {
        return `/consgts/${this.consignment}`;
    }


    /* SQL Relationships */
    @ManyToOne(() => Consignment, { mapToPk: true, hidden: true })
    consignment!: Consignment;
}

可以通过MikroORM查询构建器的子查询和显式连接来实现，无需直接写原生SQL片段，具体代码如下：

const em = this.em;

// 构建第一个子查询：筛选最近指定分钟内的GPS日志
const subQueryG = em.createQueryBuilder(GpsLog, 'g')
  .select('*')
  .where('g.logTime >= DATE_SUB(NOW(), INTERVAL 232324324 MINUTE)');

// 构建第二个子查询：按运单分组取每个运单的最新GPS日志ID
const subQueryMax = em.createQueryBuilder(GpsLog, 'gl')
  .select(['MAX(gl.gpsLogId) as gps_id', 'gl.consignment as consignment_consignment_id'])
  .groupBy('gl.consignment');

// 主查询：连接两个子查询
const rawResult = await em.createQueryBuilder()
  .select('*')
  .from(subQueryG, 'g')
  .join(subQueryMax, 'max', 'max.gps_id = g.gpsLogId')
  .getResultList();

// 将查询结果映射为GpsLog实体
const gpsLogs = rawResult.map(row => em.map(GpsLog, row));

return gpsLogs;

关键说明： #

• 用createQueryBuilder分别构建两个子查询，直接使用实体属性名（如logTime、gpsLogId），MikroORM会自动映射到对应的数据库字段。
• 主查询通过from(subQueryG, 'g')将第一个子查询作为数据源，再用join(subQueryMax, 'max', 'max.gps_id = g.gpsLogId')完成关联。
• 最后用getResultList()获取原始结果，通过em.map映射为实体实例，保留实体的属性和方法。

这种写法既符合MikroORM的使用规范，又精准复现了目标SQL的逻辑，比直接操作Knex原生API更简洁易维护。

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