I would advise dealing with the EventRegistration objects separately:
for user, registration in session.query(User, EventRegistration)…..
# process
that way you don’t need to use contains_eager() and the rows you want are
explicitly present. Collections aren’t really meant for filtered querying
across many rows like that.
> On Nov 12, 2014, at 12:30 AM, 翁哲 <[email protected]> wrote:
>
> Hi Michael,
>
> Thanks for your promote reply. And I did some experiments on it and seems the
> contains_eager helps me get exactly what I want, however it will lose effect
> whenever I committed the current session. I guess this contains_eager is
> session related. And in my case, I need to commit/rollback within the same
> loop. For example:
>
> for db_user in session.query(User).join(User.br
> <http://user.br/>_event_registrations).filter(EventRegistrations.eventId <
> 10).all():
> try:
> #here are some update process
> except Exception:
> session.rollback()
> else:
> session.commit()
>
> Any suggestions on this? Thanks!
>
>
> On Wednesday, November 12, 2014 11:53:23 AM UTC+11, Michael Bayer wrote:
>
>> On Nov 11, 2014, at 5:37 PM, 翁哲 <[email protected] <>> wrote:
>>
>> Hi all,
>>
>> Recently I found an issue on using the back reference. Here a simple
>> scenario.
>>
>> Table user (with primary key userId) and table event_registration (with two
>> foreign keys, userid referencing the user table and eventId referencing the
>> event table, as primary key). And we define the relationship for this two as
>> fk_user on event_registration and br_event_registrations on user table.
>>
>> user_table = Table("user", metadata,
>> Column('userId', BigInteger,
>> Sequence('user_userId_seq'),primary_key=True),
>> ...
>> )
>>
>> class User(object):
>> pass
>>
>> mapper(User, user_table, properties={})
>>
>> event_registration_table = Table("event_registration", metadata,
>> Column('userId', BigInteger, ForeignKey('user.userId'),
>> primary_key=True),
>> Column('eventId', BigInteger, ForeignKey('event.eventId'),
>> primary_key=True),
>> ...
>> )
>>
>> class EventRegistration(object):
>> pass
>>
>> mapper(EventRegistration, event_registration_table, properties={
>> 'fk_user': relationship(User, backref='br_event_registrations'),
>> ...
>> })
>>
>>
>> A common search query about getting the users who have registered a certain
>> or a series of events. For example:
>> for db_user in
>> session.query(User).join(User.br_event_registrations).filter(EventRegistrations.eventId
>> < 10).all():
>> db_event_registration_list = db_user.br_event_registrations
>>
>> In the db_event_registration_list, I expect to get all the registrations for
>> a certain user and with eventId < 10. However what I actually get are all
>> the registrations related to this user.
>>
>> I have tried using the joinedload and joinedload_all, but it do not solve
>> the problem.
>>
>> I'm wondering if there are some flaws on this database schema or on the
>> fetching process itself.
>
> everything you do with Query as far as join(), filter(), etc. is only about
> the primary rows you’ve asked it to load, in this case, User rows. when
> you deal with some_user.br_event_registrations, that is the collection of all
> registrations associated with this user as defined by the relationship.
>
> If you’d like to affect the actual loading of those registrations at query
> time, you can apply the join() you have to the collection using
> contains_eager(). See
> http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html#routing-explicit-joins-statements-into-eagerly-loaded-collection
>
> <http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html#routing-explicit-joins-statements-into-eagerly-loaded-collection>.
> The collection for each User object should not be previously loaded already.
>
>
>
>
>
>>
>> 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 <http://googlegroups.com/>.
>> To post to this group, send email to sqlal...@ <>googlegroups.com
>> <http://googlegroups.com/>.
>> Visit this group at http://groups.google.com/group/sqlalchemy
>> <http://groups.google.com/group/sqlalchemy>.
>> For more options, visit https://groups.google.com/d/optout
>> <https://groups.google.com/d/optout>.
>
>
> --
> 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]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at http://groups.google.com/group/sqlalchemy
> <http://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
