On Wed, Feb 28, 2018 at 3:44 PM, Adam Olsen <[email protected]> wrote:
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
When doing this, is there any way to join it so I can select the entire
room object? For instance, I would like to be able to do something like:
rooms = session.query(...).all()
for room in rooms:
print room.id, room.name, room.next_event.id, room.next_event.title,
room.next_event.start, room.next_event.end
I guess what I'm asking, is there a way to create a custom relationship
between a "Room" and it's next "Event" using a lateral join like this?
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
