Grouped result of multiple joins

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

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

@tiangolo

@tiangolo any updates
@terencetwuo

Navigation Menu

Repository navigation

Grouped result of multiple joins #118

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Grouped result of multiple joins #118

Description

First Check

Commit to Help

Example Code

Description

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Activity

yasamoka commented on Oct 11, 2021

masreplay commented on Jan 6, 2022

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions