How to print a model instance including a joined attribute?

[m0wer]

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 Product(SQLModel, table=True):
    id: str = Field(primary_key=True)
    ean: str
    slug: str
    brand: Optional[str]
    name: str
    price: float
    category_id: int = Field(foreign_key="category.id")
    description: Optional[str]
    origin: Optional[str]
    packaging: Optional[str]
    unit_name: Optional[str]
    unit_size: Optional[float]
    is_variable_weight: bool = False
    is_pack: bool = False

    images: List["ProductImage"] = Relationship(back_populates="product")
    price_history: List["PriceHistory"] = Relationship(back_populates="product")
    nutritional_information: Optional["NutritionalInformation"] = Relationship(
        back_populates="product"
    )

class NutritionalInformation(SQLModel, table=True):
    id: int = Field(default=None, primary_key=True)
    product_id: str = Field(foreign_key="product.id")
    calories: Optional[float]
    total_fat: Optional[float]
    saturated_fat: Optional[float]
    polyunsaturated_fat: Optional[float]
    monounsaturated_fat: Optional[float]
    trans_fat: Optional[float]
    total_carbohydrate: Optional[float]
    dietary_fiber: Optional[float]
    total_sugars: Optional[float]
    protein: Optional[float]
    salt: Optional[float]

    product: Product = Relationship(back_populates="nutritional_information")

# ...

@api_router.get("/products/{product_id}")
def get_product(product_id: str, session: Session = Depends(get_session)):
    result = session.exec(
        select(Product, NutritionalInformation)
        .join(NutritionalInformation)
        .where(Product.id == product_id)
    ).first()
    
    if result is None:
        raise HTTPException(status_code=404, detail="Product not found")
    
    product, nutritional_info = result
    
    return {
        **product.dict(),
        "nutritional_information": nutritional_info.dict()
    }

Description

What's a better way of doing this?

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.22

Python Version

3.11

Additional Context

No response

[YuriiMotov]

You need to create "public" model that reflects db model, but redefines relationship fields (that you want to expose) as follows (use inheritance to reduce code duplication):

class ProductBase(SQLModel):
    id: str = Field(primary_key=True)
    ean: str
    slug: str
    brand: Optional[str]
    name: str
    price: float
    category_id: int = Field(foreign_key="category.id")
    description: Optional[str]
    origin: Optional[str]
    packaging: Optional[str]
    unit_name: Optional[str]
    unit_size: Optional[float]
    is_variable_weight: bool = False
    is_pack: bool = False

class Product(ProductBase, table=True):
    images: List["ProductImage"] = Relationship(back_populates="product")
    price_history: List["PriceHistory"] = Relationship(back_populates="product")
    nutritional_information: Optional["NutritionalInformation"] = Relationship(
        back_populates="product"
    )

class ProductPublic(ProductBase):
    nutritional_information: Optional["NutritionalInformationPublic"] = None

And then validate data using this "public" model:

return ProductPublic.model_validate(product)

Or use this model as response model and just return product.

You can also use selectinload to load nested fields (relationships) in one go:

    result = session.get(
        Product,
        product_id,
        options=[
            selectinload(Product.nutritional_information),
        ],
    )

Final endpoint (I didn't run this code):

@api_router.get("/products/{product_id}")
def get_product(product_id: str, session: Session = Depends(get_session)) -> ProductPublic:
    result = session.get(
        Product,
        product_id,
        options=[
            selectinload(Product.nutritional_information),
        ],
    )
    
    if result is None:
        raise HTTPException(status_code=404, detail="Product not found")
    
    return result