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 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 sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/9459b07f-99e9-4c34-9542-6a240f16ac4an%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/9459b07f-99e9-4c34-9542-6a240f16ac4an%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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/37438116-8c8f-48fa-8036-0b394245169f%40app.fastmail.com.
