Get select with options (selectinload) using response schema

[ProgrammingStore]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue 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

from pydantic import BaseModel

class Parent(SQLModel, table=True):
    id: UUID = sm.Field(UUID, primary_key=True)
    childs:List[Child]= sm.Relationship(
        back_populates="parent"
    )

class Child(SQLModel, table=True):
    parent_id:UUID=sm.Field()
        sa_column=sm.Column(
            sm.ForeignKey("parentr.id")
    )
    parent: "Parent" = sm.Relationship(
        back_populates="childs"
    )

#read schemas

class IChildRead(BaseModel):
    id:UUID

class IParentReadWithChilds(BaseModel):
    childs:List[IChildRead]

Description

What i want to get from pydantic response schema ? I want to get a query with select lazy options, using the schema of response, because this information already contains in relationships of pydantic models.
For example:
select(Parent).options(selectinload(Parent.Childs)),

The existance of property in pydantic model gives information about the need to use selectinload. Is there any solutions for it?

Operating System

Linux

Operating System Details

any

SQLModel Version

any

Python Version

any

Additional Context

any

[cycledriver]

There are 2 ways to Child load with a selectin (or whatever type of lazy option):

1. Define the lazyload option in your model:

    childs:List[Child]= sm.Relationship(
        back_populates="parent",
        sa_relationship_kwargs: {"lazyload": "selectin"},
    )

Whenever you select Parent objects, the children will always be loaded.

2. At query time

If you want to be a bit more selective about when you do the selectin load, you can add it to the query:

from sqlalchemy.orm import selectinload

all_parents = session.exec(select(Parent).options(selectinload(Parent.childs)).all()

Pretty much anything you can do with the loads is described here:

https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html

[Bewinxed]

There are 2 ways to Child load with a selectin (or whatever type of lazy option):

1. Define the lazyload option in your model:

    childs:List[Child]= sm.Relationship(
        back_populates="parent",
        sa_relationship_kwargs: {"lazyload": "selectin"},
    )

Whenever you select Parent objects, the children will always be loaded.

2. At query time

If you want to be a bit more selective about when you do the selectin load, you can add it to the query:

from sqlalchemy.orm import selectinload

all_parents = session.exec(select(Parent).options(selectinload(Parent.childs)).all()

Pretty much anything you can do with the loads is described here:

https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html

Any way to use this without type errors? I'm getting this complaint when I use the selectinload

No overloads for "exec" match the provided argumentsPylance[reportCallIssue](https://github.com/microsoft/pyright/blob/main/docs/configuration.md#reportCallIssue)
session.py(50, 15): Overload 2 is the closest match
Argument of type "List[PersonaTrait]" cannot be assigned to parameter "keys" of type "_AttrType" in function "selectinload"
  Type "List[PersonaTrait]" is incompatible with type "_AttrType"
    "List[PersonaTrait]" is incompatible with "QueryableAttribute[Any]"
    "List[PersonaTrait]" is incompatible with type "Literal['*']"Pylance[reportArgumentType](https://github.com/microsoft/pyright/blob/main/docs/configuration.md#reportArgumentType)

[Vr3n]

In version 0.0.21 Passing lazy instead of lazyload worked for me.

 childs:List[Child]= sm.Relationship(
        back_populates="parent",
        sa_relationship_kwargs: {"lazy": "selectin"}, # lazy instead of lazyload
    )

[koldakov]

There are 2 ways to Child load with a selectin (or whatever type of lazy option):

1. Define the lazyload option in your model:

    childs:List[Child]= sm.Relationship(
        back_populates="parent",
        sa_relationship_kwargs: {"lazyload": "selectin"},
    )

Whenever you select Parent objects, the children will always be loaded.

2. At query time

If you want to be a bit more selective about when you do the selectin load, you can add it to the query:

from sqlalchemy.orm import selectinload

all_parents = session.exec(select(Parent).options(selectinload(Parent.childs)).all()

Pretty much anything you can do with the loads is described here:

https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html

@cycledriver Thank you for your reply! But I have related question, in that case all childs will be loaded, what can be a problem if there are a lot of childs. Do you know if there is a way to limit childs, or add where condition or whatever to load only N childs?

[koldakov]

There are 2 ways to Child load with a selectin (or whatever type of lazy option):

1. Define the lazyload option in your model:

    childs:List[Child]= sm.Relationship(
        back_populates="parent",
        sa_relationship_kwargs: {"lazyload": "selectin"},
    )

Whenever you select Parent objects, the children will always be loaded.

2. At query time

If you want to be a bit more selective about when you do the selectin load, you can add it to the query:

from sqlalchemy.orm import selectinload

all_parents = session.exec(select(Parent).options(selectinload(Parent.childs)).all()

Pretty much anything you can do with the loads is described here:
https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html

@cycledriver Thank you for your reply! But I have related question, in that case all childs will be loaded, what can be a problem if there are a lot of childs. Do you know if there is a way to limit childs, or add where condition or whatever to load only N childs?

Actually found a way:

all_parents = session.exec(select(Parent).options(selectinload(Parent.childs), with_loader_criteria(Child, Forum.id < N)).all()

[chriscarrollsmith]

Any way to use this without type errors? I'm getting this complaint when I use the selectinload

No overloads for "exec" match the provided argumentsPylance[reportCallIssue](https://github.com/microsoft/pyright/blob/main/docs/configuration.md#reportCallIssue)
session.py(50, 15): Overload 2 is the closest match
Argument of type "List[PersonaTrait]" cannot be assigned to parameter "keys" of type "_AttrType" in function "selectinload"
  Type "List[PersonaTrait]" is incompatible with type "_AttrType"
    "List[PersonaTrait]" is incompatible with "QueryableAttribute[Any]"
    "List[PersonaTrait]" is incompatible with type "Literal['*']"Pylance[reportArgumentType](https://github.com/microsoft/pyright/blob/main/docs/configuration.md#reportArgumentType)

You can wrap your relationship's type declaration in Mapped[]:

persona_traits: Mapped[List[PersonaTrait]] = Relationship(back_populates="persona")

That will shut Pyright up.

[PAVaillancourt-IvadoLabs]

For those searching:
from sqlalchemy.orm import Mapped

[Raymondo97]

I brought this up in a similar discussion: #1853.

The only issue with using Mapped is that it seems that SQLModel’s goal is to eliminate some of that boilerplate code that SQLAlchemy had. And SQLModel uses the Python types for interpreting the Pydantic and database column types. I’ve read that some people will add a TYPE_CHECKING flag (from Pydantic) to use it just for silencing the IDE, without affecting runtime.