NESTED JOIN

[garryismael]

How to make a join like this?

{
  "data": [
    {
      "id": 0,
      "candidate": {
        "id": 1,
        "user": {
          "id": 0,
          "name": "User Userson",
          "username": "userson",
          "email": "[email protected]",
          "is_superuser": true
        }
      },
      "job_offer": {
        "title": "Software Engineer",
        "reference": "REF12345",
        "localization": "New York",
        "city": "New York",
        "contract_type": "Fixed Term",
        "tasks": "Develop software applications",
        "experience": "3 years",
        "training": "Bachelor's Degree in Computer Science",
        "start_on": "2024-08-01",
        "expired_on": "2024-12-31",
        "id": 0,
        "industry_sector": {
          "name": "Technology",
          "id": 0
        },
        "company": {
          "name": "Tech Solutions",
          "email": "[email protected]",
          "phone": "+1234567890",
          "function": "Information Technology Services",
          "id": 0
        },
        "is_valid": true,
        "is_closed": true
      }
    }
  ],
  "total_count": 0,
  "has_more": true,
  "page": 0,
  "items_per_page": 0
}

[igorbenav]

Try this:

jobs = await crud_job_offer.get_multi_joined(
    db=session,
    schema_to_select=JobOfferReadSchema,
    joins_config=[
        JoinConfig(
            model=Candidate,
            join_on=JobOffer.candidate_id == Candidate.id,
            schema_to_select=CandidateReadSchema,
            join_type="left",
        ),
        JoinConfig(
            model=IndustrySector,
            join_on=JobOffer.industry_sector_id == IndustrySector.id,
            schema_to_select=IndustrySectorReadSchema,
            join_type="left",
        ),
        JoinConfig(
            model=Company,
            join_on=JobOffer.company_id == Company.id,
            schema_to_select=CompanyReadSchema,
            join_type="left",
        )
    ],
    nest_joins=True
)

It would be better if I had the actual models and schemas

[garryismael]

@igorbenav Im trying to read JobApplicationReadSchema not JobOfferReadSchema.

Here is the models and schemas

# User
class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(
        "id",
        autoincrement=True,
        nullable=False,
        unique=True,
        primary_key=True,
        init=False,
    )

    name: Mapped[str] = mapped_column(String(30))
    username: Mapped[str] = mapped_column(String(20), unique=True, index=True)
    email: Mapped[str] = mapped_column(String(50), unique=True, index=True)
    hashed_password: Mapped[str] = mapped_column(String)
    role: Mapped[str] = mapped_column(String(10), unique=True, index=True)

    uuid: Mapped[uuid_pkg.UUID] = mapped_column(
        default_factory=uuid_pkg.uuid4, unique=True
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default_factory=lambda: datetime.now(UTC)
    )
    updated_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), default=None
    )
    deleted_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), default=None
    )
    is_deleted: Mapped[bool] = mapped_column(default=False, index=True)

# Candidate
class Candidate(Base):
    __tablename__ = "candidates"

    id: Mapped[int] = mapped_column(
        "id",
        autoincrement=True,
        nullable=False,
        unique=True,
        primary_key=True,
        init=False,
    )

    user_id: Mapped[int] = mapped_column(
        ForeignKey("users.id"), index=True, nullable=False
    )

    uuid: Mapped[uuid_pkg.UUID] = mapped_column(
        default_factory=uuid_pkg.uuid4, unique=True
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default_factory=lambda: datetime.now(UTC)
    )
    updated_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), default=None
    )
    deleted_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), default=None
    )
    is_deleted: Mapped[bool] = mapped_column(default=False, index=True)

    __table_args__ = (UniqueConstraint("user_id"),)


# Job Offer
class JobOffer(Base):
    __tablename__ = "job_offers"

    id: Mapped[int] = mapped_column(
        "id",
        autoincrement=True,
        nullable=False,
        unique=True,
        primary_key=True,
        init=False,
    )

    title: Mapped[str] = mapped_column(String(30), index=True, nullable=False)
    reference: Mapped[str | None] = mapped_column(
        String(20), nullable=True, index=True
    )
    localization: Mapped[str] = mapped_column(String(30), nullable=False)
    city: Mapped[str] = mapped_column(String(30), nullable=False)
    contract_type: Mapped[str] = mapped_column(String(30), nullable=False)
    tasks: Mapped[str] = mapped_column(Text, nullable=False)
    experience: Mapped[str | None] = mapped_column(String(80), nullable=True)
    training: Mapped[str] = mapped_column(Text, nullable=False)
    is_valid: Mapped[bool] = mapped_column(index=True)
    is_closed: Mapped[bool] = mapped_column(index=True)

    industry_sector_id: Mapped[int] = mapped_column(
        ForeignKey("industry_sectors.id"), index=True, nullable=False
    )
    company_id: Mapped[int] = mapped_column(
        ForeignKey("companies.id"), index=True, nullable=False
    )

    start_on: Mapped[date] = mapped_column(Date, nullable=False)
    expired_on: Mapped[date] = mapped_column(Date, nullable=False)

    uuid: Mapped[uuid_pkg.UUID] = mapped_column(
        default_factory=uuid_pkg.uuid4, unique=True
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default_factory=lambda: datetime.now(UTC)
    )
    updated_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), default=None
    )
    deleted_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), default=None
    )
    is_deleted: Mapped[bool] = mapped_column(default=False, index=True)

# Industry Sector
class IndustrySector(Base):
    __tablename__ = "industry_sectors"

    id: Mapped[int] = mapped_column(
        "id",
        autoincrement=True,
        nullable=False,
        unique=True,
        primary_key=True,
        init=False,
    )
    name: Mapped[str] = mapped_column(String(30), index=True)

    uuid: Mapped[uuid_pkg.UUID] = mapped_column(
        default_factory=uuid_pkg.uuid4, unique=True
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default_factory=lambda: datetime.now(UTC)
    )
    updated_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), default=None
    )
    deleted_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), default=None
    )
    is_deleted: Mapped[bool] = mapped_column(default=False, index=True)

# Company
class Company(Base):
    __tablename__ = "companies"

    id: Mapped[int] = mapped_column(
        "id",
        autoincrement=True,
        nullable=False,
        unique=True,
        primary_key=True,
        init=False,
    )

    name: Mapped[str] = mapped_column(String(30), index=True)
    email: Mapped[str] = mapped_column(String(50))
    phone: Mapped[str] = mapped_column(String(20))
    function: Mapped[str] = mapped_column(String(50))

    uuid: Mapped[uuid_pkg.UUID] = mapped_column(
        default_factory=uuid_pkg.uuid4, unique=True
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default_factory=lambda: datetime.now(UTC)
    )
    updated_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), default=None
    )
    deleted_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), default=None
    )
    is_deleted: Mapped[bool] = mapped_column(default=False, index=True)
    
 # Schemas
 
 # Candidate 
 class CandidateReadSchema(BaseModel):
    id: Annotated[
        int,
        Field(
            description="The unique identifier of the candidate", examples=[1]
        ),
    ]
    user: Annotated[
        UserReadSchema,
        Field(description="The user details associated with the candidate"),
    ]

# User Schema
class UserReadSchema(BaseModel):
    id: int

    name: Annotated[
        str, Field(min_length=2, max_length=50, examples=["User Userson"])
    ]
    username: Annotated[
        str,
        Field(
            min_length=2,
            max_length=20,
            pattern=r"^[a-z0-9]+$",
            examples=["userson"],
        ),
    ]
    email: Annotated[EmailStr, Field(examples=["[email protected]"])]
    role: Annotated[
        UserRole,
        Field(
            description="User role", examples=[UserRole.ADMIN, UserRole.CLIENT]
        ),
    ]

class JobOfferReadSchema(JobOfferBaseSchema):
    id: Annotated[
        int, Field(description="The unique identifier of the company")
    ]
    industry_sector: Annotated[
        IndustrySectorReadSchema,
        Field(
            ...,
            description="The industry sector to which the job offer belongs.",
        ),
    ]
    company: Annotated[
        CompanyReadSchema,
        Field(..., description="The company offering the job."),
    ]
    is_valid: Annotated[
        bool,
        Field(
            description="Whether the job offer is valid",
            examples=[True, False],
        ),
    ]
    is_closed: Annotated[
        bool,
        Field(
            description="Whether the job offer is closed",
            examples=[True, False],
        ),
    ]
   
   class IndustrySectorReadSchema(IndustrySectorBaseSchema):
      model_config = ConfigDict(from_attributes=True)
  
      id: int = Field(
          None, description="The unique identifier of the industry sector"
      )
    
class CompanyReadSchema(CompanyBaseSchema):
      model_config = ConfigDict(from_attributes=True)
  
      id: int = Field(None, description="The unique identifier of the company")

 class JobApplicationReadSchema(BaseModel):
    id: int
    candidate: CandidateReadSchema
    job_offer: JobOfferReadSchema