On Wed, Feb 28, 2018 at 5:44 PM, Adam Olsen <arol...@gmail.com> 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)
you want to LEFT OUTER JOIN on that LATERAL so you'd want to use
query.outerjoin, somthing like:
from sqlalchemy import true
session.query(...).outerjoin(event_include,
true()).filter(Room.company_id == company_id)
Core is a little different but you can see the usage of join() in
http://docs.sqlalchemy.org/en/latest/core/tutorial.html#lateral-selects
>
> 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.
--
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.
