How to set a relationship count in Response Model / Schema

[nadavof]

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

class ChildBase(SQLModel):
    name: str
    parent_id: int | None = Field(default=None, foreign_key="parent.id") 

class Child(ChildBase, table=true):
    id: int | None  = Field(default=None, primary_key=True)

class ParentBase(SQLModel):
    name: str

class Parent(ParentBase, table=true):
    id: int | None  = Field(default=None, primary_key=True)
    children: list["Child"] = Relationship(back_populates="parent")

class ParentRead(ParentBase):
    children = list["Child"]

Description

I want to read the parent with the number of related children, but I don't want to read the child content.
Is there any way to define a field in SQLModel for counting the amount of relationships?

For example, something like that

class ParentRead(ParentBase):
    children_counter: int = len(list["Child"])

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.11

Additional Context

No response

[jd-solanki]

Why not just do "count/scalar" on DB when needed instead of creating seperate col for it?

[nadavof]

Thanks for the response.
I don't want to create a separate column, I want to add a field in the Data Model (for using it in FastAPI response_model) for the count.

[jd-solanki]

Are you looking for something like this: https://medium.com/@wucean/how-can-we-use-default-factory-in-a-pydantic-field-3c5d62f624d6

[nadavof]

I know the default-factory, but how can I use it for counting the amount of Child records linked to Parent? (count the relationships)
Thanks again for your help

[jplacht]

I would also recommend not solving this in the database, but with Pydantic by upgrading to the latest SQLModel that supports Pydantic V2. You would then be able to define a model that is transforming your database ParentRead utilizing computed fields with some minor adjustments.

Below an example for a computed field if the Read model inherits the list of children:

from pydantic import computed_field

class ParentRead(ParentBase):
    @computed_field
    @property
    def children_counter(self) -> int:
        return len(self.children)