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.