SQLAlchemy & Complex Queries
- by user356594
I have to implement ACL for an existing application.
So I added the a user, group and groupmembers table to the database.
I defined a ManyToMany relationship between user and group via the association table groupmembers.
In order to protect some ressources of the app (i..e item) I added a additional association table auth_items which should be used as an association table for the ManyToMany relationship between groups/users and the specific item.
item has following columns:
user_id -- user table
group_id -- group table
item_id -- item table
at least on of user_id and group_id columns are set. So it's possible to define access for a group or for a user to a specific item.
I have used the AssociationProxy to define the relationship between users/groups and items.
I now want to display all items which the user has access to and I have a really hard time doing that. Following criteria are used:
All items which are owned by the user should be shown (item.owner_id = user.id)
All public items should be shown (item.access = public)
All items which the user has access to should be shown (auth_item.user_id = user.id)
All items which the group of the user has access to should be shown.
The first two criteria are quite straightforward, but I have a hard time doing the 3rd one.
Here is my approach:
clause = and_(item.access == 'public')
if user is not None:
clause = or_(clause,item.owner == user,item.users.contains(user),item.groups.contains(group for group in user.groups))
The third criteria produces an error.
item.groups.contains(group for group in user.groups)
I am actually not sure if this is a good approach at all.
What is the best approach when filtering manytomany relationships?
How I can filter a manytomany relationship based on another list/relationship?
Btw I am using the latest sqlalchemy (6.0) and elixir version
Thanks for any insights.
