How to use LEFT JOIN LATERAL in typeorm?

user_11754273 0
Viewed 916

I want to use below query in TypeOrm but can't find a way to convert it to TypeOrm.

Any help is appreciated.

SELECT * FROM blocked_times bt 
 LEFT JOIN LATERAL (
      SELECT * FROM bookings bk WHERE bt."startTime" < bk."endTime"
 ) bk ON bk."clinicId" = bt."clinicId"
typeorm postgresql
2 Answers

I was fighting this issue for a couple of hours and found a hack

      queryBuilder.leftJoin("(SELECT 1)", "dummy", "TRUE LEFT JOIN LATERAL (SELECT * FROM bookings bk WHERE bt.startTime < bk.endTime) bk ON bk.clinicId = bt.clinicId");

This is probably not the exact solution but should help you to get the general idea

user_2861380

This is my solution.

const subQuery = `SELECT * FROM bookings bk WHERE bt."startTime" < bk."endTime"`;
queryBuilder.leftJoin((qb) => {
    qb.getQuery = () => `LATERAL (${subQuery})`;
    qb.setParameters({});
    return qb;
  },
  `bk`,
  `bk."clinicId" = bt."clinicId"`
)
user_167043310
Related