'How to use LEFT JOIN LATERAL in typeorm?

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"
postgresqltypeorm


Solution 1:[1]

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

Solution 2:[2]

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"`
)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 TrejGun
Solution 2 Tr?n Nhàn

Related Questions