[M2M Models] Create M2M relationship without explicitly back_populating the links

[Pk13055]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from datetime import datetime
from enum import IntEnum, auto, unique
from typing import Optional

from sqlmodel import Field, Relationship, SQLModel


@unique
class ProjectRole(IntEnum):
    """Member roles"""

    admin = auto()
    owner = auto()


class ProjectMember(SQLModel, table=True):
    """Project member model"""

    project_id: Optional[int] = Field(
        default=None, primary_key=True, foreign_key="project.id"
    )
    user_id: Optional[int] = Field(
        default=None, primary_key=True, foreign_key="user.id"
    )

    role: ProjectRole = Field(default=ProjectRole.admin)


class Project(SQLModel, table=True):
    """Project model"""

    id: Optional[int] = Field(default=None, primary_key=True)
    created_on: datetime = Field(default=datetime.utcnow(), nullable=False)

    members: list["User"] = Relationship(
        back_populates="projects", link_model=ProjectMember
    )


class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    projects: list[Project] = Relationship(
        back_populates="members", link_model=ProjectMember
    )


@router.get("/", tags=["fetch"], response_model=list[ProjectBase])
async def get_projects(
    title: str | None = None, user: User = Depends(get_user), db: DB = Depends(get_db)
) -> list[ProjectBase]:
    """Get all projects"""
    query = (
        select(Project, ProjectMember, User)  # NOTE: this query is what I need help with
        .join(ProjectMember, ProjectMember.project_id == Project.id)
        .join(User, ProjectMember.user_id == User.id)
        .where(ProjectMember.user_id == user.id)
    )
    if title:
        query = query.where(Project.title == title)
    query = query.order_by(Project.modified_on.desc())
    return (await db.execute(query)).unique().scalars().all()

Description

Given this basic model structure I want to fetch List[Project] such that it has members populated with User objects, and even better if they can be flattened such that members: list[CustomUser] where CustomUser._fields = dict(**ProjectMember._fields, **User._fields)

• create the given models
• Try out the logic of get_projects
• How to achieve the above?

Operating System

Linux, macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

Python 3.11.3

Additional Context

No response