如何使用Mikro-ORM实现关联表的条件更新查询？

问题背景 #

能正常执行的SQL多表更新语句，转成Mikro-ORM写法一直报错，之前查过相关方案但expr方法已废弃，求正确写法。

可用的SQL语句 #

UPDATE schema.control_subs AS s
SET cancel_date = CASE 
        WHEN :input_date < c.start_date THEN c.start_date
        ELSE :input_date
    END
FROM schema.controls AS c
WHERE s.control_id = c._id
AND s.control_id IN (:controlIds);

尝试过的错误Mikro-ORM代码 #

await this.em
  .createQueryBuilder(ControlSubs)
  .update({
    cancel_date: this.em
      .createQueryBuilder(Controls, "c")
      .select(
        // raw(`CASE WHEN ${cancelDate} < c.start_date THEN c.start_date ELSE ${cancelDate} END`)
        raw(
            `CASE WHEN 
                TIMESTAMP ${cancelDate
                    .toISOString()} AT TIME ZONE 'UTC' < c.start_date 
                THEN c.start_date 
                ELSE TIMESTAMP ${cancelDate.toISOString()} AT TIME ZONE 'UTC' END`,
        ),
      )
      .where("c._id = s.control_id"),
  })
  .where({
    s.control_id: { $in: controlIds },
  });

正确写法 #

核心是用from方法关联另一张表，同时用参数化raw避免SQL注入，代码如下：

await this.em
  .createQueryBuilder(ControlSubs, 's')
  .update({
    cancel_date: raw(`CASE WHEN ? < c.start_date THEN c.start_date ELSE ? END`, [cancelDate, cancelDate])
  })
  .from(Controls, 'c')
  .where('s.control_id = c._id')
  .andWhere({ 's.control_id': { $in: controlIds } })
  .execute();

关键说明 #

• 用.from(Controls, 'c')关联controls表，对应SQL里的FROM子句
• 参数化传递cancelDate，别直接拼字符串，防止SQL注入
• 显式指定表别名s和c，避免关联时歧义
• 必须调用.execute()才能执行更新操作（之前的代码漏掉了这步）

如果要处理时区，调整raw表达式即可：

raw(`CASE WHEN TIMESTAMP ? AT TIME ZONE 'UTC' < c.start_date THEN c.start_date ELSE TIMESTAMP ? AT TIME ZONE 'UTC' END`, [cancelDate.toISOString(), cancelDate.toISOString()])

内容的提问来源于stack exchange，提问作者mc-user