How to express Postgresql CROSS JOIN LATERAL in Typeorm

user_10444409 0
Viewed 521

I'm using Typeorm as my ORM and I don't know how to express cross join lateral in Typeorm

I have 2 tables: WorkingTime m-1 Worker

WorkingTime entity:

  • dayOfWeek: Int
  • periods: jsonb
  • worker: (Many to one) relationship with Worker table

Below is periods json structure:

[{
  start: "10:00:00",
  end: "13:00:00
}, {
  start: "15:00:00",
  end: "21:00:00"
}]

I wanted to get all the Workers whose workingTimes contain periods that have start < 12:00:00

and I used cross join lateral

SELECT DISTINCT "worker".id
FROM "worker" "worker"
LEFT JOIN "working_time" "workingTimes" 
ON "workingTimes"."workerId"="worker"."id" 
CROSS JOIN LATERAL
     jsonb_array_elements("workingTimes"."periods")AS attr
WHERE attr ->> 'start' < '12:00:00'

But I don't know how to express the query using Typeorm (NestJS)

typeorm nestjs postgresql
1 Answers

you did not mention but I guess you are using query builder!

There is no honest way of doing this, but... I have found a hack

      queryBuilder.leftJoin("(SELECT 1)", "dummy", "TRUE CROSS JOIN LATERAL jsonb_array_elements("workingTimes"."periods") attr ON TRUE");

I did not test it with your data but hope you got the general idea

user_2861380
Related