Repository navigation
Get select with options (selectinload) using response schema #538
Description
Activity
- changed the title
[-]Get select options (joinedload or selectinload) using response schema[/-][+]Get select options (selectinload) using response schema[/+]on Jan 25, 2023 - changed the title
[-]Get select options (selectinload) using response schema[/-][+]Get select with options (selectinload) using response schema[/+]on Jan 25, 2023 There are 2 ways to Child load with a selectin (or whatever type of lazy option):
- 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.
- 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
Reacted by Mehdi Hayani MechkouriReacted by Viren PatelThere are 2 ways to Child load with a selectin (or whatever type of lazy option):
- 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.
- 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)Reacted by tomerezer, Joaquin Bravo Contreras, Ryan, Felix Wunderlich, Lucas Colombo, Navaneethan, Viren Patel, Tom Leverstone, mushishiva, Kiyo Kunii and 8 moreReacted by Gautham Ganeshan ChettiarReacted by Patrick Wspanialy, Heiko Zeus, Ahmed Abdullah , Joaquin Bravo Contreras, Tyler, Lucas Colombo, NAOE Kenichi, Tom Leverstone, Krishna Penukonda, Kiyo Kunii and 6 moreIn version
0.0.21Passinglazyinstead oflazyloadworked for me.childs:List[Child]= sm.Relationship( back_populates="parent", sa_relationship_kwargs: {"lazy": "selectin"}, # lazy instead of lazyload )
Reacted by Vitor SchirmerThere are 2 ways to Child load with a selectin (or whatever type of lazy option):
- 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.
- 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?
There are 2 ways to Child load with a selectin (or whatever type of lazy option):
- 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.
- 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()Reacted by Mehdi Hayani MechkouriAny 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.
Reacted by Richard G- locked and limited conversation to collaborators
on Aug 15, 2025
First Check
Commit to Help
Example Code
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