Got it: query = session.query(Store.Store) query = query.join('userGroups', 'users') query = query.filter(User.User.id == int(userId)) print str(query.all())
>From the examples inside the sqlalchemy egg (http://prdownloads.sourceforge.net/sqlalchemy/SQLAlchemy-0.6.6.tar.gz?download) In the examples/association/basic_association.py file. Nice!. P.S.: Now I'm a little bit down, because I've spent one whole day figuring out something that is explained inside a file called "basic_association"... :-D What will be an "advanced_association"?? 2011/3/16 Hector Blanco <white.li...@gmail.com>: > Hello everyone! > > I am reopening that because now I want to go an step further... And > I'm having troubles. > > Let's say I have an Store class that has a relationship pointing to > UserGroup that has a relationship pointing to Users. > > I'm trying to create a method getStoresByUserId(parameterUserId) that, > if I pass a numeric user id as a parameter, would give me a list of > the stores that said user "can see". I have modeled it like: > > class Store(declarativeBase): > __tablename__ = "stores" > > _name = Column("name", String(50)) > > _userGroups = relationship("UserGroup", secondary=user_group_store, > order_by=lambda:UserGroup.name, > primaryjoin=lambda: Store.id == user_group_store.c.store_id, > secondaryjoin=lambda: UserGroup.id == > user_group_store.c.user_group_id, > collection_class=set > ) > > class UserGroup(declarativeBase): > __tablename__ = "user_groups" > > _name = Column("name", String(50)) > #_users: Backref from User > > class User(declarativeBase): > __tablename__ = "users" > > _firstName = Column("first_name", String(50)) > _lastName = Column("last_name", String(50)) > _userName = Column("user_name", String(50), unique=True, > nullable=False) > _password = Column("password", String(64), nullable=False) > > _userGroupId = Column("user_group_id", Integer, > ForeignKey("user_groups.id")) > _userGroup = relationship("UserGroup", uselist=False, > backref=backref("_users", > collection_class=set > )) > > So, in the method I want to create, > (getStoresByUserId(parameterUserId) or something like that) I > understand that I have to load the Store.userGroups, then load the > users of the UserGroup(s) and then check that that User.id == > parameterId > > I have tried: > query = query.select_from(join(Store.Store.userGroups, > UserGroup.UserGroup, UserGroup.UserGroup.users, > User.User).filter(User.User.id == int(parameterId))) > > ...and... erm... several other thousands of combinations like that... > Without luck. With that, I get: > AttributeError: Neither 'property' object nor 'function' object has an > attribute 'corresponding_column' > > In some other cases I get "Store"s (instances) but they are not > properly filtered. It looks like it's getting all the stores assigned > to any userGroup, without filtering by the user id... > > Now I'm kind of lost. > > Thank you in advance! > > 2011/3/16 Hector Blanco <white.li...@gmail.com>: >> Hello everyone! >> >> In my application I have a class "Store" that can contain several >> "UserGroup"s (for permission purposes) and one "UserGroup" can belong >> to several "Stores". >> >> I want to get the "Stores" that contain a certain "UserGroup" (instance): >> >> I have it modeled like this: >> >> class Store(declarativeBase): >> __tablename__ = "stores" >> >> _id = Column("id", Integer, primary_key=True) >> _name = Column("name", String(50)) >> _number = Column("number", Integer) >> >> _storeGroupId = Column("store_group_id", Integer, >> ForeignKey("store_groups.id")) >> >> >> # _devices: Backref from Device >> >> _userGroups = relationship("UserGroup", secondary=user_group_store, >> order_by=lambda:UserGroup.UserGroup.name, >> primaryjoin=lambda: Store.id == user_group_store.c.store_id, >> secondaryjoin=lambda: UserGroup.UserGroup.id == >> user_group_store.c.user_group_id, >> collection_class=set >> ) >> >> And: >> >> class UserGroup(declarativeBase): >> __tablename__ = "user_groups" >> >> _id = Column("id", Integer, primary_key=True) >> _name = Column("name", String(50)) >> #_users: Backref from User >> >> I want to create a method (something like >> getStoresByUserGroup(userGroup) ) that accepts a userGroup instance >> (or id) and returns only the stores that contain that "userGroup". >> >> That should allow me to "hide" certain stores for certain user groups. >> The use case is: The user who is currently logged into my application >> will belong to a certain user group. If he wants to access the >> "stores" stored in the database, he will only see the ones that have >> that user's userGroup among the Store._userGroups set. >> >> I'm trying to join the Store with the UserGroup, but then I get: >> "Can't find any foreign key relationships between 'stores' and >> '%(175967212 user_groups)s >> >> I'm also trying to use alias, but without any luck so far. >> >> Do you have any idea, hint... Whatever. I'm kind of lost here. I keep >> trying things without knowing very well what I'm doing. >> >> Thank you in advance >> > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.