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_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] <javascript:>> 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.
>
> 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 [email protected] <javascript:>.
> To post to this group, send email to [email protected]
> <javascript:>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit 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.
