Search code examples
sqljoinsqlalchemypython-elixir

SQLAlchemy & Complex Queries

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.

Solution

  • 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])