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