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] sqlalchemy.orm.exc.FlushError on subclass
Thanks. We went with the helper class route, and it seems to be working
much better than attempting to use inheritance in a manner that seems
unsupported.
On Wednesday, September 6, 2023 at 1:24:04 PM UTC-5 Mike Bayer wrote:
> if you can't correct this model to apply the persistence details to the
> concrete class you wish to persist and query, then you'd do the suggested
> "enable_typechecks=False". There is no attribute in SQLAlchemy named
> "meta" and no stack trace is given here so I dont know to what that refers.
>
> Overall I'm not sure how this API_Person class is useful because you can't
> query for them. I would think that if you cant change the original
> model then you'd have this API_Person as a series of helper functions that
> accept a Person as their argument.
>
>
>
> On Wed, Sep 6, 2023, at 1:56 PM, 'Luna Lucadou' via sqlalchemy wrote:
>
> The project I am working on is split up into several modules. Previously,
> each module had its own ORM classes.
> However, due to several bugs arising from forgetting to update each
> module's ORM classes in lock step when adding new functionality, we have
> decided it would be best to extract the ORM classes which interact with our
> DB into their own module and make that available to the other modules via
> pip.
>
> One of these modules, which monitors for changes in an upstream DB and
> applies them to ours, has some methods which are not present in the other
> modules and which cannot be easily extracted out due to its dependencies on
> upstream DB functionality.
>
> As such, in this module, we must subclass the ORM models which interact
> with our DB:
>
> models.apimodels.db.person.py:
> #...
> @dataclass(init=False, eq=True, unsafe_hash=True)
> class Person(Base):
> __tablename__ = "person"
>
> id: Mapped[int] = mapped_column(primary_key=True)
> first_name: Mapped[str]
> last_name: Mapped[str]
> email_address: Mapped[str]
> office_address: Mapped[str]
> office_phone_number: Mapped[str]
>
> # ...
>
> etl.models.api_db.api_person.py:
> #...
> from apimodels.db.person import Person as PersonBase
> # ...
> class API_Person(PersonBase):
> __tablename__ = "person"
> __table_args__ = {"keep_existing": True}
>
> def get_pvi(self):
> # ...
>
> def get_historical_pvis(self) -> list[str]:
> # ...
>
> def __eq__(self):
> # ...
>
> def __hash__(self):
> # ...
>
> @staticmethod
> def from_upstream_hub_person(
> uh_person: Optional[UH_Person],
> ) -> Optional["API_Person"]:
> # ...
>
> Of note is that this subclass does not add any new attributes or modify
> existing ones, it merely adds some helper methods related to identifying
> primary key changes in the upstream DB. (This is also why we override the
> eq and hash methods provided by dataclasses - incoming changesets have to
> be matched against existing records, even when primary keys change
> upstream.)
>
> This is effectively single-table inheritance, but it is not a good fit for
> polymorphic_identity since it is not a distinct class, merely adding
> module-specific helper methods, and if I am reading the documentation
> correctly, using polymorphic_identity would mean any records touched by
> the API_Person subclass (which is all of them) would no longer be usable
> by other modules, which do not extend any of the models.
>
> From what I have read, it seems like the keep_existing param should be of
> use here, but neither it nor extend_existing set to True help with the
> errors I am seeing when attempting to interact with this subclass in any
> way:
>
> sqlalchemy.orm.exc.FlushError: Attempting to flush an item of type 'model.api_db.api_person.API_Person'> as a member of collection
> "Identifier.person". Expected an object of type 'apimodels.db.person.Person'> or a polymorphic subclass of this type. If
> is a subclass of 'apimodels.db.person.Person'>, configure mapper "Mapper[Person(person)]" to
> load this subtype polymorphically, or set enable_typechecks=False to allow
> any subtype to be accepted for flush.
>
> I did try setting enable_typechecks to False, but this results in a
> different error when attempting to use getattr on the subclass:
>
> AttributeError: 'Person' object has no attribute 'meta'
>
> Is there a better way of doing this?
>
>
> --
> 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+...@googlegroups.com.
> To view this discussion on the web visit
>
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(
