hey there -
great test script, thanks for making this easy.
using modern SQLAlchemy versions your script outputs the SELECT:
SELECT source.id AS source_id
FROM source JOIN (poly_parent JOIN poly_child ON poly_parent.id =
poly_child.id) ON source.id = poly_child.parent_id
If you are seeing a SELECT subquery this may be because you are on an older
version of SQLite (or an extremely old SQLAlchemy version, like older than
0.9). SQLAlchemy from 0.9 through the 1.3 series has a workaround for SQLite's
lack of support for nested JOIN phrases, but this only impacts what are now
extremely old SQLite versions (prior to 3.7.17).Up until maybe SQLAlchemy
1.1 or so the logic was not sensitive to SQLite's version since they hadn't
fixed it yet.
Overall this logic is removed in 1.4 which would mean if you have an old
version of SQLite the above query would just fail. some background is at:
https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#removed-join-rewriting-logic-from-sqlite-dialect-updated-imports
assuming this is what's happening your steps are:
1. make sure you're on SQLAlchemy 1.3 or 1.4beta
2. make sure SQLite is recent, newer than 3.7.16
On Tue, Nov 3, 2020, at 7:27 AM, Alex Collins wrote:
> Trying to configure a set of relationships to all be joined loaded and the
> particular relationship structure doesn’t seem to want to join. I have a
> one-to-many relationship where the many is the child in joined table
> inheritance. The foreign key to my source table is on the polymorphic child
> table. But, however I configure the relationship it does a subquery instead
> of a joined load on the parent class.
>
> Built a test application as a demonstration. What I want is to have the
> script below function the same way but, the query at the end outputs a
> joinedload for PolyParent instead of a subquery.
>
> from sqlalchemy import Column, ForeignKey, Integer, Text, create_engine
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship, sessionmaker
>
> Base = declarative_base()
>
> class PolyParent(Base):
> __tablename__ = "poly_parent"
> id = Column(Integer, primary_key=True)
> type = Column(Text)
> __mapper_args__ = {"polymorphic_identity": "poly_parent",
> "polymorphic_on": type}
>
> class PolyChild(PolyParent):
> __tablename__ = "poly_child"
> id = Column(Integer, ForeignKey("poly_parent.id"), primary_key=True)
> parent_id = Column(Integer, ForeignKey("source.id"))
> __mapper_args__ = {"polymorphic_identity": "poly_child"}
>
> class Source(Base):
> __tablename__ = "source"
> id = Column(Integer, primary_key=True)
> children = relationship(PolyChild)
>
> engine = create_engine("sqlite://")
> session = sessionmaker(bind=engine)()
> Base.metadata.create_all(bind=engine)
>
> print(session.query(Source).join(Source.children))
>
> --
> 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/70b48614-d1c0-42f4-8792-ed96f181915dn%40googlegroups.com
>
> .
--
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/1ea3bbd2-481b-46c8-b363-15ebccca71c4%40www.fastmail.com.
