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.
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.