Re: [sqlalchemy] How to combine statement eager loading with polymorphic relations
the recursion_depth feature on selectinload() is also very new and that is
actually a very complex and not very mature feature.If you only wanted to
apply additional options for N levels deep, you would build out separate
options for that, like:
options(
selectinload(Model.thing, recursion_depth=-1),
defaultload(Model.thing).selectinload(Model.otherthing).selectinload(Model.otherthing)
)
something like that
again, fairly esoteric stuff
On Fri, Sep 15, 2023, at 4:25 AM, Cornelis Poppema wrote:
> 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])
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, you are coming off of a parent loader option
> already. So following from this, the correct form based on a direct
> reading of those docs would, *in theory*, be:
>
> select().options(
> selectinload(Parent.target).options(
> selectin_polymorphic(Target,
Re: [sqlalchemy] How to combine statement eager loading with polymorphic relations
working on that issue but you should also be able to do this right now:
.options(
selectinload(StepModel.actionbases.of_type(ServiceActionModel)).selectinload(ServiceActionModel.service),
raiseload("*"),
)
that produces more of a LEFT OUTER JOIN with a subquery situation but still
"works"
On Thu, Sep 14, 2023, at 7:36 AM, Cornelis Poppema wrote:
> 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
Re: [sqlalchemy] How to combine statement eager loading with polymorphic relations
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, you are coming off of a parent loader option already. So
following from this, the correct form based on a direct reading of those docs
would, *in theory*, be:
select().options(
selectinload(Parent.target).options(
selectin_polymorphic(Target, [TargetSubclassA]),
selectinload(TargetSubclassA.elements)
)
)
that is, you can build up sibling options from a parent loader option using
another call to .options().
however, this doesn't work; the validation of the loader chain unsurprisingly
notes that TargetSubclass is not linked from Parent.target, and they'd like you
to use of_type() instead.So I've made a bug for this here:
https://github.com/sqlalchemy/sqlalchemy/issues/10348 as something has to
change here, either the docs, or the usage pattern for selectin_polymorphic(),
or the error checks have to get a lot smarter to figure this out and let this
case pass, since it works fine the way you are expecting if I just have it skip
the error checking.
What you can do now is use with_polymorphic() instead that is more configurable
for this kind of loader chain:
TT = with_polymorphic(Target, [TargetSubclassA])
select().options(selectinload(Parent.target.of_type(TT).selectinload(TT.TargetSubclassA.elements)))
discussion can continue at the issue above or a new github discussion, thanks!
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a99d9853-8936-455f-9439-bfe5f2e03faa%40app.fastmail.com.
[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(
