Cr*p!... 5 minutes after writing, I got it: query = session.query(Store.Store).select_from(join(Store.Store, UserGroup.UserGroup, Store.Store.userGroups)).filter(UserGroup.UserGroup.id == int(userGroupId))
http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins Well... it may help someone :) 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.
