Is it possible to populate relationship's collection with only query-filtered results using contains_eager()?

[Юрий Пайков]

When I do a query like this

result = session.query(A).\
join(A.bs).\
join(B.cs).\
filter_by(C.somedata.in_([4455, 4466])).\
options(contains_eager(A.bs).contains_eager(B.cs)).one()

and list related C objects

for b in result.bs:
   b.cs

I get all related to B C objects instead of only those with 4455, 4466 values - even though I filtered the output of the statement.

So eager loading when used with contains_eager() - as I understand - shouldn't load other related objects apart from those which are selected by statement. Still I get Cs with values other that 4455, 4466(i.e. all the collection) :( Could any suggest from where come other Cs? Thanks in advance

[Michael Bayer]

Юрий Пайков <dia...@cry5tal.in> wrote:

> When I do a query like this
>
> result = session.query(A).\
> join(A.bs).\
> join(B.cs).\
> filter_by(C.somedata.in_([4455, 4466])).\
> options(contains_eager(A.bs).contains_eager(B.cs)).one()
>
> and list related C objects
>
> for b in result.bs:
> b.cs
>
> I get all related to B C objects instead of only those with 4455, 4466 values - even though I filtered the output of the statement.

turn on echo=True that will show what SQL is being emitted. That one() might be adding a LIMIT to the query that you don’t want, and the contains_eager() might be failing to connect to the collections, and thus they are lazy loaded after the fact.

>
> So eager loading when used with contains_eager() - as I understand - shouldn't load other related objects apart from those which are selected by statement. Still I get Cs with values other that 4455, 4466(i.e. all the collection) :( Could any suggest from where come other Cs? Thanks in advance
>
>

> --
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[dia...@cry5tal.in]

Ok, I've only put one() here for an example. I saw the query, there is no LIMIT. And it selects only filtered results, as i wanted.
 But I' m still unsure if i get it right about how SQLalchemy treat contains_eager-does it load data for mentioned relationships only from query which we declare this option to?

Отправлено с моего Андроида

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/Kpzqn81Kbcc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Michael Bayer]

dia...@cry5tal.in wrote:

> Ok, I've only put one() here for an example. I saw the query, there is no LIMIT. And it selects only filtered results, as i wanted.
> But I' m still unsure if i get it right about how SQLalchemy treat contains_eager-does it load data for mentioned relationships only from query which we declare this option to?

oh right, its first() that does the LIMIT.

Anyway, yes, contains_eager says, “populate these collections”. the usage looks correct, so the echo=True, or even echo=‘debug’ which will show you the result rows coming in, will show you where its going wrong.

or if those collections were already populated previously within that Session, that would affect it also. you can say query.populate_existing() to force it to re-load everything.

[Юрий Пайков]

Well, I added "echo=True" and saw the queries - no additional were made. 

Then I added 

populate_existing()

 and out of the blue I started to get the desired result, but frankly I don't quite understand why. What side-effects this could bring?

суббота, 31 января 2015 г., 21:22:08 UTC+5 пользователь Michael Bayer написал:

Anyway, yes, contains_eager says, “populate these collections”.   the usage looks correct, so the echo=True, or even echo=‘debug’ which will show you the result rows coming in, will show you where its going wrong.

or if those collections were already populated previously within that Session, that would affect it also.   you can say query.populate_existing() to force it to re-load everything.

 and out of the blue I started to get the desired result, but frankly I don't quite understand why

суббота, 31 января 2015 г., 21:22:08 UTC+5 пользователь Michael Bayer написал:

[Michael Bayer]

Юрий Пайков <dia...@cry5tal.in> wrote:

> Well, I added "echo=True" and saw the queries - no additional were made.
> Then I added
> populate_existing()
>
> and out of the blue I started to get the desired result, but frankly I don't quite understand why. What side-effects this could bring?

it means the parent records in question were already loaded, and already present in the identity map for your session - the collections were there already, and the contains_eager had no effect on their contents.

>
>
> суббота, 31 января 2015 г., 21:22:08 UTC+5 пользователь Michael Bayer написал:
>
> Anyway, yes, contains_eager says, “populate these collections”. the usage looks correct, so the echo=True, or even echo=‘debug’ which will show you the result rows coming in, will show you where its going wrong.
>
> or if those collections were already populated previously within that Session, that would affect it also. you can say query.populate_existing() to force it to re-load everything.
>
>
> and out of the blue I started to get the desired result, but frankly I don't quite understand why
>
>
> суббота, 31 января 2015 г., 21:22:08 UTC+5 пользователь Michael Bayer написал:
>
> Anyway, yes, contains_eager says, “populate these collections”. the usage looks correct, so the echo=True, or even echo=‘debug’ which will show you the result rows coming in, will show you where its going wrong.
>
> or if those collections were already populated previously within that Session, that would affect it also. you can say query.populate_existing() to force it to re-load everything.
>
>
>

[Юрий Пайков]

Thanks Michael for your attention!

понедельник, 2 февраля 2015 г., 2:56:51 UTC+5 пользователь Michael Bayer написал: