Prevent garbage collection of the joined record

[romantolkachyov]

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 PatientsRepository:
    async def fetch_one(self, pk: UUID | None = None, **kwargs: Any) -> tuple[Patient, User]:
        stmt = select(Patient, User).join(User).where(Patient.id == pk)
        result = await self.db.exec(stmt)
        obj = result.first()
        return obj

    async def create(self, obj: Patient) -> tuple[Patient, None]:
        self.db.add(obj)
        await self.db.flush()
        patient, user = await self.fetch_one(pk=obj.id)
        self._loaded_related.append(user)
        return patient, None

Description

I'm using a select with join to retrieve related model.

After I return parent model from the repository method, the child model garbage collected (and it sound reasonable). So patient.user will lead to additional query to the database (or exception in my case cause I use async session).

To solve this, I store related model to the class property _loaded_related to preserve reference and prevent garbage collection.

Are there any other clear way to do so? Don't want to return both records from the repository method cause I want a clear interface (and will still have to deal with gc above on the stack anyway).

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.22

Python Version

Python 3.12.7

Additional Context

I believe there is no issue with SQLModel of SQLAlchemy, just trying to find a better approach.

[YuriiMotov]

Use selectinload to properly pre-load relationships:

    async def fetch_one(self, pk: UUID | None = None, **kwargs: Any) -> Patient:
        return await session.get(Patient, pk, options=[selectinload(Patient.user)])