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.

Reply via email to