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