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` so that it'll return all rooms, but I'm having a real hard time
figuring out how to do that part.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
