Re: [sqlalchemy] How to combine statement eager loading with polymorphic relations
Hello Mike, thank you very much for the in-depth reply and providing a
solution :)
There is no specific reason that I posted in google groups, I did not visit
the support page, this was just something I decided after thinking where to
post first between googlegroups and stackoverflow, I will use github
discussions when I open a new discussion in the future!
Your workaround will help me continue so many many thanks. I thought I
tried all variations, but I can only concede after seeing this work that I
did not attempt adding the selectinload to the root options().
It might warrant a new thread, but can you also tell me if there is a way
to control the options() that sqlalchemy uses for the recursion of
next_step ?
Ie. I included this bit in the original post:
```
.options(
selectinload(StepModel.next_step, recursion_depth=-1),
...
)
```
and it would be helpful to know if I can chain the same options to
next_step, so that step also has its actions and their relationships()
eagerly available etc.
PS
FWIW the reason I included `raiseload("*")` in options() is because I am
running async queries, and personally the error I will be confronted with
trying to access lazy attributes is more helpful, so I've come to add it by
default. Without raisedload(*) I would see:
```
sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't
call await_only() here. Was IO attempted in an unexpected place?
(Background on this error at: https://sqlalche.me/e/20/xd2s)
```
With the help of raisedload(*) I get to see:
```
sqlalchemy.exc.InvalidRequestError: 'ServiceActionModel.service' is not
available due to lazy='raise```
```
This helps me tackle those cases more easily one-by-one.
On Thursday, 14 September 2023 at 15:30:23 UTC+2 Mike Bayer wrote:
>
>
> On Thu, Sep 14, 2023, at 7:36 AM, Cornelis Poppema wrote:
>
> Hi all,
>
>
> I am new to sqlalchemy, I think the idea of what I am trying to achieve is
> relatively simple, but I can't seem to figure out how to retrieve
> `.service` in the same query. I failed to find an example in the 2.0
> documentation for exactly this.
>
> My attempts have been to simply chain a .selectinload after the
> .selectin_polymorphic, ie.:
>
> ```python
> .options(
> selectinload(StepModel.next_step, recursion_depth=-1),
> selectinload(StepModel.actionbases).selectin_polymorphic(
> [
> ServiceActionModel,
> ],
> )
> .selectinload(ServiceActionModel.service),
> raiseload("*"),
> )
> ```
>
> This gives the error:
>
> ```
> /usr/local/lib/python3.11/site-packages/sqlalchemy/orm/strategy_options.py:2442:
>
> in _raise_for_does_not_link
> raise sa_exc.ArgumentError(
> E sqlalchemy.exc.ArgumentError: ORM mapped entity or attribute
> "ServiceActionModel.service" does not link from relationship
> "StepModel.actionbases". Did you mean to use
> "StepModel.actionbases.of_type(ServiceActionModel)"?
> ```
>
>
> Hi -
>
> A few up front things, is it possible you can post these questions that
> have a lot of code on github discussions? That's at
> https://github.com/sqlalchemy/sqlalchemy/discussions .I'm sort of
> wondering how new users are still arriving here at the mailing list, did
> you find this list via the support page at
> https://www.sqlalchemy.org/support.html ? I would want to change the
> verbiage there to please refer people to GH discussions instead.
> Especially with these advanced inheritance eager loading problems, which in
> the majority of cases end up being real bugs in SQLAlchemy, as seems to be
> the case here (at least, there is an inconsistency in the API that somehow
> needs to be documented, or something).
>
> As for the question, first off this is really advanced usage and I've
> hardly ever seen people using selectin_polymorphic(), much less deep within
> a chain of loaders like this.
>
> The correct form for this load would follow from how it's described at
> https://docs.sqlalchemy.org/en/20/orm/queryguide/inheritance.html#combining-additional-loader-options-with-selectin-polymorphic-subclass-loads
>
> , where the ORM allows the selectin_polymorphic(Target, [TargetSubclassA])
> to be a sibling to the appropriate relationship load,
> selectinload(TargetSubclassA.elements). The example there places both of
> these options comma-separated within select().options().This is the
> "inconsistent" part because I'm already surprised the ORM is allowing the
> selectinload() to be present against TargetSubclassA when that's not one of
> the primary entities in the select().
>
> However in your case
[sqlalchemy] How to combine statement eager loading with polymorphic relations
Hi all,
I am struggling to combine a joinedload (or selectinload, whatever works)
with the submodels of selectin_polymorphic.
I have a model "step" that has a collections of "actions" that are relevant
when my application reaches that step. These actions can be anything and
also have their own relationships to other models that I want to eagerly
load, all while querying "step". I would strongly prefer to achieve this in
a query statement over defining eager loading in the relationship
declarations on the models itself.
Here are my models:
```python
from enum import StrEnum, auto
from sqlalchemy import Column, Enum, ForeignKey, Integer, String
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import as_declarative, declared_attr, relationship
class ActionTypeEnum(StrEnum):
flow = auto()
service = auto()
transition = auto()
@as_declarative()
class BaseSqlModel:
pk = Column(Integer, primary_key=True, index=True)
class IdColumnMixin:
@declared_attr
def id(cls):
return Column(UUID(as_uuid=True), unique=True, nullable=False,
index=True)
class StepModel(IdColumnMixin, BaseSqlModel):
__tablename__ = "step"
next_step_id = Column(ForeignKey("step.id", use_alter=True))
next_step = relationship("StepModel", remote_side="StepModel.id")
actionbases = relationship("ActionBaseModel")
class ActionBaseModel(IdColumnMixin, BaseSqlModel):
__tablename__ = "actionbase"
action_type = Column(Enum(ActionTypeEnum), nullable=False)
step_id = Column(ForeignKey("step.id"), nullable=False)
step = relationship("StepModel", back_populates="actionbases")
__mapper_args__ = {
"polymorphic_identity": "actionbase",
"polymorphic_on": "action_type",
}
class ServiceModel(IdColumnMixin, BaseSqlModel):
__tablename__ = "service"
name = Column(String(200), nullable=False)
class ServiceActionModel(ActionBaseModel):
__tablename__ = "serviceaction"
id = Column(ForeignKey("actionbase.id"), primary_key=True)
service_id = Column(ForeignKey("service.id"), nullable=True)
service = relationship("ServiceModel")
__mapper_args__ = {
"polymorphic_identity": ActionTypeEnum.service,
}
```
To query step I write this:
```python
db_step = (
await self.session.execute(
select(StepModel)
.filter_by(id=id)
.options(
selectinload(StepModel.next_step, recursion_depth=-1),
selectinload(StepModel.actionbases).selectin_polymorphic(
[
ServiceActionModel,
],
),
raiseload("*"),
)
)
).scalar_one()
```
Accessing `db_step.actionbases[0]` works as expected: it is of type
ServiceActionModel, accessing `db_step.actionbases[0].service.name` throws
the expected error:
```
/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/strategies.py:862:
in _invoke_raise_load
raise sa_exc.InvalidRequestError(
E sqlalchemy.exc.InvalidRequestError: 'ServiceActionModel.service' is not
available due to lazy='raise'
```
I am new to sqlalchemy, I think the idea of what I am trying to achieve is
relatively simple, but I can't seem to figure out how to retrieve
`.service` in the same query. I failed to find an example in the 2.0
documentation for exactly this.
My attempts have been to simply chain a .selectinload after the
.selectin_polymorphic, ie.:
```python
.options(
selectinload(StepModel.next_step, recursion_depth=-1),
selectinload(StepModel.actionbases).selectin_polymorphic(
[
ServiceActionModel,
],
)
.selectinload(ServiceActionModel.service),
raiseload("*"),
)
```
This gives the error:
```
/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/strategy_options.py:2442:
in _raise_for_does_not_link
raise sa_exc.ArgumentError(
E sqlalchemy.exc.ArgumentError: ORM mapped entity or attribute
"ServiceActionModel.service" does not link from relationship
"StepModel.actionbases". Did you mean to use
"StepModel.actionbases.of_type(ServiceActionModel)"?
```
Which seems fair; there is no relationship defined on ServiceActionModel to
StepModel. (but there is on ActionBaseModel).
So I've tried part 2 of the hint in the exception, using `of_type`. Again I
failed to find much about this feature in the documentation; from what I
can tell it is used in combination with join on a select(poly-base-model)
to be able to .where() on subclass-specific models, but besides one source
that actually uses it in a joinedload() I have not find any examples.
It is very possible, or perhaps likely these concepts are documented and I
don't know the right terms of keywords to look for.
I have tried the following:
```python
db_step = (
await self.session.execute(
select(StepModel)
.filter_by(id=id)
.options(
