How to combine statement eager loading with polymorphic relations

198 views
Skip to first unread message

Cornelis Poppema

unread,
Sep 14, 2023, 7:36:18 AM9/14/23
to sqlalchemy
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(
            selectinload(StepModel.next_step, recursion_depth=-1),
            selectinload(StepModel.actionbases).selectin_polymorphic(
                [
                    ServiceActionModel,
                ],
            )
            .options(
                selectinload(StepModel.actionbases.of_type(ServiceActionModel)).options(
                    selectinload(ServiceActionModel.service),
                )
            ),
            raiseload("*"),
        )
    )
).scalar_one()
```

but this results in:

```
/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/base.py:283: in _generative
    x = fn(self, *args, **kw)
/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/strategy_options.py:1174: in options
    opt._apply_to_parent(self)
/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/strategy_options.py:1129: in _apply_to_parent
    _raise_for_does_not_link(parent.path, attrname, parent_entity)
/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 "StepModel.actionbases" does not link from relationship "StepModel.actionbases".
```

At this point I suspect I have not defined the relationships() properly, but cannot find the missing piece. Am I missing something obvious ? Is there a better or clearly documented way to do what I am trying to achieve ? If this can be done, is there a way to combine it with `selectinload(StepModel.next_step, recursion_depth=-1)` to load all actions recursively as well ? If not through a query statement, is it via relationship() parameters ?

Thanks for reading, and hopefully you can help me or just as curious :)

Mike Bayer

unread,
Sep 14, 2023, 9:30:23 AM9/14/23
to noreply-spamdigest via sqlalchemy


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!


Mike Bayer

unread,
Sep 14, 2023, 1:22:10 PM9/14/23
to noreply-spamdigest via sqlalchemy


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:
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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+...@googlegroups.com.

Cornelis Poppema

unread,
Sep 15, 2023, 4:25:05 AM9/15/23
to sqlalchemy
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.

Mike Bayer

unread,
Sep 15, 2023, 8:20:07 AM9/15/23
to noreply-spamdigest via sqlalchemy
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
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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+...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages