node.jstypescriptpostgresqlmikro-orm

Mikro-orm: Update with select statement


I am trying to update a table column that is dependent on one more table. The sql query is working fine on database and couldn't figure out the mikro-orm query for that. Below are the sql and mikro-orm query that I tried. Mikro-orm isn't accepting to execute update queries as raw queries. Can someone help me or at least guide me with this.

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);
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 },
  });

I tried this How can I add a CASE expression in mikro-orm querybuilder? but the expr isnt available now.


Solution

  • I have modified the query such that the update part takes the dates based on conditions and joins the tables by using .join(). Here is the updated query.

    await this.em
      .createQueryBuilder(ControlSubs, 'cs')
      .update({
        cancel_date: raw(
          `
          CASE 
            WHEN ? < ( SELECT start_date FROM schema.controls WHERE _id = cs.control_id)
            THEN (SELECT start_date FROM schema.controls WHERE _id = cs.control_id)
            ELSE ?
          END
          `,
          [cancelDate, cancelDate],
        ),
      }).join('cs.control_id', 'c')
      .where({
        s.control_id: { $in: controlIds },
      });
    

    It would be great if someone review it and provide any suggestions on improving it.