Grouped result of multiple joins

[terencetwuo]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue 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

def get_model_detail(session: Session, id: int):
    data = session.query(ModelProfile, SubModelProfile).join(SubModelProfile, ModelProfile.id  == SubModelProfile.mp_id).filter(ModelProfile.id == id).all()

    return data

Description

• Above code is trying to retrieve one-to-many relationship of ModelProfile [1:M] SubModelProfile
• It results in duplicate ModelProfile in the result:

[
  {
    "ModelProfile": {
      "model_version": "1",
      "id": 1,
      "created_at": "2021-09-15T14:01:52"
    },
    "SubModelProfile": {
      "mp_id": 1,
      "id": 1
    }
  },
  {
    "ModelProfile": {
      "model_version": "1",
      "id": 1,
      "created_at": "2021-09-15T14:01:52"
    },
    "SubModelProfile": {
      "mp_id": 1,
      "id": 2
    }
  },
  {
    "ModelProfile": {
      "model_version": "1",
      "id": 1,
      "created_at": "2021-09-15T14:01:52"
    },
    "SubModelProfile": {
      "mp_id": 1,
      "id": 3
    }
  }
]

Question: I'm expecting the result to look like:

[
  {
    "ModelProfile": {
      "model_version": "1",
      "id": 1,
      "created_at": "2021-09-15T14:01:52"
    },
    "SubModelProfile": [
       {
        "mp_id": 1,
        "id": 1
      }, {
       "mp_id": 1,
       "id": 2
      }, {
      "mp_id": 1,
      "id": 3
      }
   ]
  }
]

Or this (what I wish to achieve)

[
  {
    "model_profile": {
      "model_version": "1",
      "id": 1,
      "created_at": "2021-09-15T14:01:52"
      "sub_model_profiles":  [
           {
            "mp_id": 1,
            "id": 1
          }, {
           "mp_id": 1,
           "id": 2
          }, {
          "mp_id": 1,
          "id": 3
          }
      ]
  }
]

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.7.11

Additional Context

No response

[yasamoka]

This is an SQL question, not related to SQLModel.

Here, you are selecting from both tables with a join on both tables:

data = session.query(ModelProfile, SubModelProfile).join(SubModelProfile, ModelProfile.id  == SubModelProfile.mp_id).filter(ModelProfile.id == id).all()

Let us focus on ModelProfile.id and SubModelProfile.id for the sake of simplicity.

Say you have 3 ModelProfiles and 3 SubModelProfiles for each ModelProfile.

When you join the tables, the rows look like this:

ModelProfile.id | SubModelProfile.id
              1                    1
              1                    2
              1                    3
              2                    1
              2                    2
              2                    3
              3                    1
              3                    2
              3                    3

However, since you have a condition that ModelProfile.id should only match id, you get the first 3 rows.

To avoid repetition, do not include ModelProfile in your SELECT clause - that is, do not include it in .query(). Just keep ModelProfile in your JOIN clause - that is, include ModelProfile only in .join().

SQLAlchemy should be able to infer how to join the two tables. It should be enough to use select(SubModelProfile).join(ModelProfile).

[masreplay]

@tiangolo any updates
@terencetwuo

[YuriiMotov]

You can specify relationships between these models:

class ModelProfile(ModelProfileBase, table=True):
    ...
    sub_model_profiles: list["SubModelProfile"] = Relationship(back_populates="model_profile")

class SubModelProfile(SQLModel, table=True):
    ...
    mp_id: int = Field(foreign_key="modelprofile.id")
    model_profile: ModelProfile = Relationship(back_populates="sub_model_profiles")

And then fetch it just like this:

mp1_db = session.get(ModelProfile, 1)

or with select and eager loading:

        mp1_db = session.exec(
            select(ModelProfile)
            .where(ModelProfile.id == 1)
            .options(selectinload(ModelProfile.sub_model_profiles))
        ).one()

Runnable code example in the details:

from typing import Optional

from sqlmodel import Field, Relationship, Session, SQLModel, create_engine, select
from sqlalchemy.orm import selectinload

engine = create_engine("sqlite:///")


def create_db_and_tables():
    SQLModel.metadata.drop_all(engine)
    SQLModel.metadata.create_all(engine)
    with Session(engine) as session:
        mp1 = ModelProfile(
            model_version="1", sub_model_profiles=[SubModelProfile(), SubModelProfile()]
        )
        mp2 = ModelProfile(
            model_version="2", sub_model_profiles=[SubModelProfile(), SubModelProfile()]
        )
        session.add_all([mp1, mp2])
        session.commit()


class ModelProfileBase(SQLModel):
    model_version: str


class ModelProfile(ModelProfileBase, table=True):
    id: Optional[int] = Field(primary_key=True, nullable=False)
    sub_model_profiles: list["SubModelProfile"] = Relationship(
        back_populates="model_profile"
    )


class ModelProfileRead(ModelProfileBase):
    id: int
    sub_model_profiles: list["SubModelProfile"]


class SubModelProfile(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True, nullable=False)
    mp_id: int = Field(foreign_key="modelprofile.id")
    model_profile: ModelProfile = Relationship(back_populates="sub_model_profiles")


def main():
    create_db_and_tables()
    with Session(engine) as session:
        mp1_db = session.get(ModelProfile, 1)
        # Or with select:
        # ----------------
        # mp1_db = session.exec(
        #     select(ModelProfile)
        #     .where(ModelProfile.id == 1)
        #     .options(selectinload(ModelProfile.sub_model_profiles))
        # ).one()
        # ----------------
        print(mp1_db.sub_model_profiles)
        print(ModelProfileRead.model_validate(mp1_db).model_dump())


if __name__ == "__main__":
    main()