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 <[email protected]>:
> 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 <[email protected]>:
>> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
