SQLAlchemy: efficient relationship loading in 3-way many-to-many relationship

Question

I want to create a simple user management database using async SQLALchemy 2.0 with the following idea:

• A user should have only one role in one project
• A user can have different roles in different projects
• A user can be part of multiple projects (which may have thousands of documents)

Here is what I have:

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')

So whenever I select a user (select(User).where(User.id == id)), the UserProjectRoleLink is a table where user_id, role_id ,project_id are loaded, while the relationships including (the circular) user relationship is not loaded. How can I load the role details as well as the project details within the same select but not loading the thousands of documents ?

in my Rest api, I would like to repond for example with a user reponse like:

{
    "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
                        }
                    ]
}

How can I preload all project and role relationships (and not just iterating through each one and dynamically load it (expensive)) but not the user realtionship as it would throw a recursion error ?

Also from the project side, I would like to query, which user has access to a particular project with which roles projectResult:

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

I have played around with joinedLoading options but I don't know how to load each user_project_roles effictively as (when coming from the user) I get a list of user_project_roles
I also thought about secondary joins but no luck

Answer 1

You can specify eager loading via chaining and also with multiple options together.

So here we load via outer join from User to UserProjectRoleLink to Project. Then we after that query is loaded we lookup the roles via the role ids we fetched in the first query. So this should result in exactly 2 SELECT statements.

q = select(
    User
).options(
    joinedload(      
        User.user_project_roles
    ).options(
        joinedload(UserProjectRoleLink.project), 
        selectinload(UserProjectRoleLink.role)
).where(User.id == user_id)

There is an example that uses these suboptions here specifying-sub-options-with-load-options

As long as you don't reference project.documents then the documents should not be eager loaded. Depending on how your serialization works, ie. jsonify, or whatever, you will need to exclude that property.

Then you could dump out your data like this:

return [{
    "id": user.id,
    "username": user.username,
    "project_roles": [{
        "project_name": upr.project.project_name, 
        "project_id": upr.project.id, 
        "role": upr.role.name,
        "role_id": upr.role.id,
    } for upr in user.user_project_roles],
} for user in session.scalars(q)]

async I think is the same just with a wrapped await

[{} for user in (await session.scalars(q))]