Hello,
I have a schema that includes these relationships:
class Airport(Base):
...
class Runway(Base):
...
airport = relationship(Airport, backref=backref("runways",
collection_class=set), lazy="joined", innerjoin=True)
...
class FlightEvent(Base):
...
runway = relationship(Runway, lazy="joined")
...
- Whenever I fetch FlightEvents, I want to automatically fetch the
runways that may be referenced by the event, hence lazy="joined" in
the runway = relationship. This relationship is an outer join because
an event may or may not reference a runway.
- Whenever I fetch Runways, I want to automatically fetch the Airport
where those runways are located, so another lazy="joined". This one is
an inner join because a runway is guaranteed to have an associated
airport.
The generated SQL contains this (formatting added):
FROM flight_events
LEFT OUTER JOIN world.runways AS runways_1
ON runways_1.runway_id = flight_events.runway_id
INNER JOIN world.airports AS airports_1
ON airports_1.airport_id = runways_1.airport_id
Unfortunately this doesn't work. Due to the INNER JOIN to airports, only
events which reference runways (which in turn reference airports) are
returned.
The only way I know to make this work is for the INNER JOIN to be
contained inside the RHS of the OUTER JOIN (where it logically belongs).
If the SQL fragment above is replace with the following, it works:
FROM flight_events
LEFT OUTER JOIN (
world.runways AS runways_1
INNER JOIN world.airports AS airports_1
ON airports_1.airport_id = runways_1.airport_id
)
ON runways_1.runway_id = flight_events.runway_id
Is there a way to convince SQLAlchemy to generate this kind of SQL?
If so, I was not able to find how. It seems that the AST given as
input to the SQL compiler has the joins defined flat, not
hierarchically (i.e. the INNER JOIN of runways and airports is not an
AST node under the RHS of the toplevel OUTER JOIN).
Using SQLAlchemy 0.6.3 (from Debian stable... yeah, I know Debian is
behind the current version) with MySQL 5.1.49.
-Phil
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
