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:
- Specifying the relationship as lazy loaded in relationship definition
- Using with_polymorphic to specify that the polymorphic tables should be loaded as a flat list.
- 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