I'm using sqlalchemy's ORM, and run queries like, I want to get all Odds
for a specific Event.
An Event has many EventMarkets, an EventMarket has many MarketParticipants,
and each MarketParticipant has many Odds.
A query like,
Odds.query
> .join(Odds.market_participant)
> .join(MarketParticipant.event_market)
> .join(EventMarket.event)
> .options(
contains_eager(Odds.market_participant)
> .contains_eager(MarketParticipant.event_market)
> .contains_eager(EventMarket.event))
> .filter(Event.id == 123456)
This produces a query like,
SELECT ...
> FROM odds
> LEFT OUTER JOIN market_participant AS market_participant_1 ON
> market_participant_1.id = odds.market_participant_id
> LEFT OUTER JOIN event_market AS event_market_1 ON event_market_1.id =
> market_participant_1.event_market_id
> LEFT OUTER JOIN event AS event_1 ON event_1.id = event_market_1.event_id
>
WHERE event.id = 123456
>
Which works perfectly, but I consider having to have both join and
contains_eager redundant, I'd much prefer to do the more straightforward,
Odds.query
> .options(
joinedload(Odds.market_participant)
> .joinedload(MarketParticipant.event_market)
> .joinedload(EventMarket.event))
> .filter(Event.id == 123456)
But this produces a query like
SELECT ...
> FROM event, odds
> LEFT OUTER JOIN market_participant AS market_participant_1 ON
> market_participant_1.id = odds.market_participant_id
> LEFT OUTER JOIN event_market AS event_market_1 ON event_market_1.id =
> market_participant_1.event_market_id
> LEFT OUTER JOIN event AS event_1 ON event_1.id = event_market_1.event_id
> WHERE event.id = 123456
>
As it queries from both Event, and then Odds with the joins to Event again,
this time as "event_1", it joins the entire table, incorrectly.
----
I'm also aware I could do all the filters,
Odds.query
> .filter(
> Odds.market_participant,
> MarketParticipant.event_market,
> EventMarket.event,
> Event.id == 123456)
>
>
But that doesn't ensure the "bits" are loaded in (market_participant for
odds, event_market for market_participant, etc.), you still need to
redundantly add the contains_eager chain,
Odds.query
> .options(contains_eager(Odds.market_participant)
> .contains_eager(MarketParticipant.event_market)
> .contains_eager(EventMarket.event))
> .filter(
> Odds.market_participant,
> MarketParticipant.event_market,
> EventMarket.event,
> Event.id == 123456)
>
>
This produces a perfectly acceptable query, with filtering via the tables,
instead of joins,
SELECT ...
>
FROM event, event_market, market_participant, odds
> WHERE market_participant.id = odds.market_participant_id
AND event_market.id = market_participant.event_market_id
> AND event.id = event_market.event_id
AND event.id = 123456
----
Again, I know coupling join with contains_eager (as is done in the first
example) works, but I absolutely consider it redundant.
This redundancy is shown more clearly if you swap contains_eager with
joinedload,
Odds.query
> .join(Odds.market_participant)
> .join(MarketParticipant.event_market)
> .join(EventMarket.event)
> .options(
joinedload(Odds.market_participant)
> .joinedload(MarketParticipant.event_market)
> .joinedload(EventMarket.event))
> .filter(Event.id == 123456)
That produces the exact same as the first query, coupling join with
contains_eager.
----
Is there any way to be able to specify what you want in joinedload in the
options without having to specify the join as well, *AND* be able to filter
on those tables, like the example.
The main reason I'm asking this is because every now and again, with a
simple autcompleted typo, or forgetfulness, I forget something, or get
something wrong in the join chain, or the contains_eager chain, and instead
of a query coming back within seconds, it stalls for a few minutes, trying
to return a million rows, instead of fifty. 99% of the time with these
mistakes one side will be correct (the join chain), with a mistake in the
other (the contains_eager chain). I understand those bugs / errors are "on
me", but even every time I do it, to do it right it frustrates me a bit.
I've put a decent amount of effort into design the tables with minimal
redundancy, both on the Db side, and within my code, but then, when I'm
running a few queries, I do get frustrated every time when I have to write
double the "join" logic, in both .join / .filter, and .contains_eager.
--
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.
