SQLAchemy ORM: LEFT JOIN LATERAL() ON TRUE

Question

I am trying to replicate the following raw query:

SELECT r.id, r.name, e.id, e.title, e.start, e.end
FROM room r
LEFT JOIN LATERAL (
    SELECT evt.id, evt.title, evt.start, evt.end
    FROM event evt, calendar cal
    WHERE
        r.calendar_id=cal.id AND evt.calendar_id=cal.id AND evt.end>%(start)s
    ORDER BY abs(extract(epoch from (evt.start - %(start)s)))
    LIMIT 1
) e ON TRUE
WHERE r.company_id=%(company_id)s;

with the SQLAlchemy ORM:

start = datetime.datetime.now()
company_id = 6

event_include = session.query(
    Event.id,
    Event.title,
    Event.start,
    Event.end) \
.filter(
    Room.calendar_id == Calendar.id,
    Event.calendar_id == Calendar.id,
    Event.end > start,
) \
.order_by(func.abs(func.extract('epoch', Event.start - start))) \
.limit(1) \
.subquery() \
.lateral()


query = session.query(Room.id, Room.name, event_include) \
.filter(Room.company_id == company_id)

Which produces the following SQL:

SELECT room.id AS room_id, room.name AS room_name, anon_1.id AS anon_1_id, anon_1.title AS anon_1_title, anon_1.start AS anon_1_start, anon_1."end" AS anon_1_end
FROM room, LATERAL (
    SELECT event.id AS id, event.title AS title, event.start AS start, event."end" AS "end"
    FROM event, calendar
    WHERE room.calendar_id = calendar.id AND event.calendar_id = calendar.id AND event."end" > %(end_1)s ORDER BY abs(EXTRACT(epoch FROM event.start - %(start_1)s)
    )
LIMIT %(param_1)s) AS anon_1
WHERE room.company_id = %(company_id_1)s

This returns all the rooms and their next calendar event, but only if there is a next calendar event available. It needs to be a LEFT JOIN LATERAL() ON TRUE, but I'm not sure how to do that.

Any help here would be great.

Answer 1

Use outerjoin with true expression

from sqlalchemy import true

query = session.query(Room.id, Room.name, event_include) \
.outerjoin(event_include, true()) \
.filter(Room.company_id == company_id)