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 :)
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/9459b07f-99e9-4c34-9542-6a240f16ac4an%40googlegroups.com.
