> On Nov 11, 2014, at 5:37 PM, 翁哲 <wengc...@gmail.com> 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+unsubscr...@googlegroups.com
> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
> To post to this group, send email to sqlalchemy@googlegroups.com
> <mailto:sqlalchemy@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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
