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