On Sun, Sep 9, 2018 at 10:01 AM, seaders <[email protected]> wrote:
> 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.
TLDR: I would accept that you find it "verbose", but it is not "redundant".
When you claim it's "redundant", that sounds like you're saying that
nobody would ever want to do a join() *without* contains_eager(), and
hence, there should be no joinedload() either. There would just be
join(), and it would always look for scalar relationships or
collections to populate with those joined results. But join() works
much more generically than this and is also agnostic of the fact that
the rows being joined constitute a scalar row, as seems to be the case
here, or a collection, where it is not a given that a user would want
a filtered set of rows from their join() to also form the population
of their collections, e.g. they'd have objects with
not-fully-populated collections. People do want to use
contains_eager() in this way, in fact, even though I find it to be
misleading. Also, join() can be expressed multiple times across
the same relationship() path, or expressed from entities that don't
actually include a given path, or from individual columns where
relationship loading doesn't apply, and in all these cases it would be
ambiguous for contains_eager() to invoke automatically, besides that
it is usually (but not always) undesirable in the collection use case.
So with join() and contains_eager(), there are two separate intents
being expressed and IMO there is no redundancy. This may however be
considered to be an undesirable verbosity.
As far as a feature that would attempt to run these two intents at
once, I think as a built in feature it would be confusing and
misleading for the reasons given above, not to mention it would fail
to function unpredictably when invoked against the same relationship
path multiple times.
The usual way to reduce query verbosity in this kind of case is that
you can make your own option that does the two things you want at the
same time. The most expedient form is to make use of a
"transformation" function:
def eager_join(criteria):
def transform(q):
return q.join(criteria).options(contains_eager(criteria))
return transform
The above function can be used as:
query = query.with_transformation(eager_join(MyModel.some_attr))
http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=query#sqlalchemy.orm.query.Query.with_transformation
Alternatively this function can be a standalone function that you pass
a Query towards, or a method on Query subclass, which you then provide
to your Session / sessionmaker using the query_cls parameter. If you
are using an existing system like Flask that already provides for a
Query class, you can still subclass that.
If you'd still like to pursue that SQLAlchemy can have such a feature,
feel free to specify the API you think it should have, however I'm at
the moment skeptical such an API wouldn't introduce many new ambiguous
scenarios that would need to be detected, when all the while a
completely unambiguous system of producing this effect already exists.
The simplest thing I can think of would be if joinedload() simply had
an option to either not run its automatic aliasing feature, or if you
could pass a desired target entity towards it so that you could filter
on it, in your example it would be:
Odds.query
.options(
joinedload(Odds.market_participant)
.joinedload(MarketParticipant.event_market)
.joinedload(EventMarket.event, target=Event))
.filter(Event.id == 123456)
Above, we tell the last joinedload() to create its JOIN against the
plain "event" table without anonymously aliasing it, so that the
filter() catches it.
This is again explciitly expressing the two separate intents, but just
in a less verbose way.
>
> --
> 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.
