Thanks for your advice. What we want is a collection of all the selected even registrations for each users. Certainly we can manipulate the process in you suggested way, with adding an extra directory, may be list, to collect all the registrations.
Besides, I see in Hibernate they have transaction and it can have multiple sessions. Does SqlAlchemy has a similar sub-session technology, although I haven't found out yet? If so I think it can solve this problem. Thanks. On Thursday, November 13, 2014 1:54:48 AM UTC+11, Michael Bayer wrote: > > > 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] <javascript:>> 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. >> >> 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]. >> 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. >> >> >> > -- > 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.
