SQLAlchemy：高效加载三元多对多关系

Question

我想用 async SQLALchemy 2.0 创建一个简单的用户管理数据库，具体想法如下：

一个用户在一个项目中只能有一个角色
一个用户可以在不同的项目中拥有不同的角色
一个用户可以参与多个项目（这些项目可能有成千上万的文档）

这是我目前的代码：

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(60), index=True, unique=True)
    user_project_roles = relationship('UserProjectRoleLink', back_populates='user')

class Project(db.Model):
    __tablename__ = 'device'
    id = db.Column(db.Integer, primary_key=True)
    project_name = db.Column(db.String(60), unique=True)
    user_project_roles = relationship('UserProjectRoleLink', back_populates='project')
    
    documents = relationship('Document', back_populates='project') # thousands of documents


class Document(db.Model)
    __tablename__ = 'device'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(60), unique=True)
    text =db.Column(db.String(60))
    project = relationship('Project', back_populates="documents")
    
class Role(db.Model):
    __tablename__ = "role"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(60), unique=True)
    user_project_roles = relationship('UserProjectRoleLink', back_populates='role')
    

class UserProjectRoleLink(db.Model):
  user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
  project_id = Column(Integer, ForeignKey('project.id'), primary_key=True)
  role_id = Column(Integer, ForeignKey('role.id'), primary_key=True)

  user = relationship('User', back_populates='user_project_roles')
  role = relationship('Role', back_populates='user_project_roles')
  project = relationship('Project', back_populates='user_project_roles')

每当我选择一个用户（使用 select(User).where(User.id == id)），UserProjectRoleLink 是一个表，其中包含 user_id、role_id 和 project_id 的信息，而用户之间的关系（包括循环关系）并没有被加载。
我该如何在同一次选择中加载角色和项目的详细信息，但又不加载成千上万的文档呢？

在我的 REST API 中，我希望能返回一个用户的响应，例如：

{
    "id": 234, 
    "username": "JohnDoe", 
    "project_roles":[
                        {
                            "project_name":"Project 1", 
                            "project_id": 1, 
                            "role:"Admin",
                            "role_id": 1
                        },
                        {   
                            "project_name":"Project 2", 
                            "project_id": 2, 
                            "role:"User",
                            "role_id": 2
                        }
                    ]
}

我该如何预加载所有项目和角色的关系（而不是逐个遍历并动态加载，这样会很耗费资源），但又不加载用户之间的关系，因为那样会导致递归错误？

另外，从项目的角度，我想查询哪些用户可以访问特定项目，以及他们的角色是什么。项目结果如下：

{
    "id":2,
    "project_name":"Project 1", 
    "users":[
                {
                    "user_id": 234,
                    "user_name": JohnDoe,
                    "role":"Admin",
                    "role_id: "1",
                }
            ] 
}

我尝试过 joinedLoading 选项，但不知道如何有效地加载每个 user_project_roles，因为当从用户那边获取时，我得到的是一个 user_project_roles 的列表。
我也考虑过 secondary joins，但没有成功。

sqlalchemy 数据库查询用户管理 rest api 多对多关系 async 角色权限预加载

SQLAlchemy：高效加载三元多对多关系

1 个回答

撰写回答