SQLAlchemy & Complex Queries

Question

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 three criteria are quite straightforward, but I have a hard time doing the 4th 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.

Answer 1

contains() method is for single item check. Assuming your group table is mapped to Group class try the following:

item.groups.any(Group.id.in_([group.id for group in user.groups])

Answer 2

Thanks Denis, your suggestion worked. I changed the datastructure a little bit, because somehow elixir is not able to map two AssociationProxy's to the same table. So i created two tables instead of one:

• auth_item_users
• auth_item_groups

and I used a ManyToMany relation instead of a AssociationProxy.

BTW does someone now why following clauses does not work:

• item.users.any(Users == user) (Error: AttributeError: 'bool' object has no attribute '_annotate')
• item.users.any(user) (Error: same _anootate error)

The only clause that works is following: item.users.any(Users.id == user.id)

Users,item ... class user ... specific User Entity

Thanks in advance