Python SQLModel reading nested One-To-Many relationship

Question

I have a request table that is linked to a request_entity table by a One-To-Many relationship. Now each request_entity has a location_id, service_id and module_id, which are also respective tables. When querying against a request, I want to be able to fetch the service, location and module for each one of the request_entities for the request.

Request Model

class Request(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    customer_email: str
    customer_name: str
    company_name: str
    social_security_number: str
    phone_number: str
    total_cost: float = Field(default=None)
    active: int = Field(default=1)
    offer_sent: int = Field(default=0)
    offer_accepted: int = Field(default=0)

    request_entities: Optional["RequestEntity"] = Relationship(back_populates="request")

Request Entities Model

class RequestEntity(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    request_id: Optional[int] = Field(default=None, foreign_key="request.id")
    location_id: Optional[int] = Field(default=None, foreign_key="location.id")
    service_id: Optional[int] = Field(default=None, foreign_key="service.id")
    module_id: Optional[int] = Field(default=None, foreign_key="module.id")
    description: str = Field(default=None, sa_column=Column(TEXT))
    area: int = Field(default=None)

    request: Optional["Request"] = Relationship(back_populates="request_entities")
    location: Optional[Location] = Relationship(back_populates="request_entities")
    service: Optional[Service] = Relationship(back_populates="request_entities")
    module: Optional[Module] = Relationship(back_populates="request_entities")

Now I want to query the location, module and service for the request_entites while fetching a request.

The query now is

statement = select(Request).where(Request.id == request_id)

Any suggestions?

Python SQLModel reading nested One-To-Many relationship

0 Answers0