On Thu, Mar 1, 2018 at 6:34 PM, Adam Olsen <[email protected]> wrote: > 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?
you might be able to map to that LATERAL, try out the pattern at http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper. > > -- > 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. -- 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.
