SQLAlchemy 和 SQLAlchemy复杂查询
我必须为现有应用程序实施 ACL。
因此，我将用户、组和组成员表添加到数据库中。
我通过关联表groupmembers定义了用户和组之间的多对多关系。
为了保护应用程序的某些资源（即item），我添加了一个额外的关联表auth_items，它应该用作之间的ManyToMany关系的关联表组/用户和特定项目。
项目具有以下列：
- user_id -->用户表
- group_id -->组表
- item_id -->项目表
至少设置了 user_id 和 group_id 列。因此可以定义组或用户对特定项目的访问权限。
我已经使用 AssociationProxy 来定义用户/组和项目之间的关系。
我现在想显示用户有权访问的所有项目，但我很难做到这一点。使用以下标准：
- 应显示用户拥有的所有项目 (item.owner_id = user.id)
- 应显示所有公共项目 (item.access = public)
- 应显示用户有权访问的所有项目 ( auth_item.user_id = user.id)
- 应显示用户组有权访问的所有项目。
前三个标准非常简单，但我很难做到第四个。
这是我的方法：
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))
第三个标准会产生错误。
item.groups.contains(group for group in user.groups)
我实际上不确定这是否是一个好方法。
过滤多对多关系时的最佳方法是什么？
我如何根据另一个列表/关系过滤多对多关系？
顺便说一句，我正在使用最新的 sqlalchemy (6.0) 和 Elixir 版本，
感谢您的任何见解。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
contains()方法用于单个项目检查。假设您的组表映射到Group类，请尝试以下操作：contains()method is for single item check. Assuming your group table is mapped toGroupclass try the following: