Joined Load Polymorphic Child with SQLAlchemy

Viewed 365

I'm using Sqlalchemy and trying to joined load the children of a table using a list relationship I've configured. The child table is a polymorphic child table of another table. So, that table should be joined as well. I'm getting a subquery instead of joined load tables as I'd like. This matters because the query is loading in a very unperformant way and the subquery is a likely culprit. Some things I've tried:

  1. Specifying the relationship as lazy loaded in relationship definition
  2. Using with_polymorphic to specify that the polymorphic tables should be loaded as a flat list.
  3. Removing the limit on the query which works although I have no idea why and would prefer another solution.

Below is a test app I've been playing with. I have two example queries of me trying to specify a joined load condition on the relationship.

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import joinedload, with_polymorphic

from app.flask_extended import Flask

db = SQLAlchemy()

class PolyParent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    type = db.Column(db.Text)

    __mapper_args__ = {
        "polymorphic_identity": "poly_parent",
        "polymorphic_on": type,
    }

class PolyChild(PolyParent):
    id = db.Column(
        db.Integer,
        db.ForeignKey("poly_parent.id"),
        primary_key=True,
    )
    parent_id = db.Column(
        db.Integer,
        db.ForeignKey(
            "lazy_load_parent.id",
        ),
    )

    __mapper_args__ = {"polymorphic_identity": "poly_child"}

class LazyLoadParent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    children = db.relationship("PolyChild", lazy="joined", uselist=True)

    __mapper_args__ = {"polymorphic_identity": "poly_parent"}


app = Flask(__name__)


with app.app_context():
    app.config['SQLALCHEMY_DATABASE_URI'] = "ENTER POSTGRES URI HERE!"
    app.config["SQLALCHEMY_ENGINE_OPTIONS"] = {"echo": True}
    db.init_app(app)
    db.create_all()

    # Query 1
    LazyLoadParent.query.filter_by(id=1).first()

    # Query 2
    polymorphic_alias = with_polymorphic(PolyParent, PolyChild, flat=True)
    LazyLoadParent.query.options(joinedload(
        LazyLoadParent.children.of_type(polymorphic_alias))
    ).filter_by(id=1).first()

    db.drop_all()

Either query results in the following SQL that contains a subquery:

SELECT
    anon_1.lazy_load_parent_id AS anon_1_lazy_load_parent_id,
    poly_child_1.id AS poly_child_1_id,
    poly_parent_1.id AS poly_parent_1_id,
    poly_parent_1.type AS poly_parent_1_type,
    poly_child_1.parent_id AS poly_child_1_parent_id
FROM (
    SELECT
        lazy_load_parent.id AS lazy_load_parent_id
    FROM
        lazy_load_parent
    WHERE
        lazy_load_parent.id = % (id_1) s
    LIMIT % (param_1) s) AS anon_1
    LEFT OUTER JOIN (poly_parent AS poly_parent_1
    JOIN poly_child AS poly_child_1 ON poly_parent_1.id = poly_child_1.id) ON anon_1.lazy_load_parent_id = poly_child_1.parent_id
0 Answers
Related