I've attached an example showing the issue. I have a class, Test, with a
single polymorphic subclass, TestOne. I also have TestChild, which has a fk
to Test, and a relation using that fk.
When I execute:
session.query(TestChild) \
.options(joinedload(TestChild.parent)) \
.get(1)
I see this query:
SELECT test_children.id AS test_children_id, test_children.test_id AS
test_children_test_id, anon_1.test_id AS anon_1_test_id, anon_1.test_type
AS anon_1_test_type, anon_1.test_name AS anon_1_test_name, anon_1.test1_id
AS anon_1_test1_id, anon_1.test1_value1 AS anon_1_test1_value1
FROM test_children LEFT OUTER JOIN (SELECT test.id AS test_id, test.type AS
test_type, test.name AS test_name, test1.id AS test1_id, test1.value1 AS
test1_value1
FROM test LEFT OUTER JOIN test1 ON test.id = test1.id) AS anon_1 ON
anon_1.test_id = test_children.test_id
WHERE test_children.id = 1
What I would like to see is this:
SELECT <whatever>
FROM test_children
LEFT OUTER JOIN test on test_children.test_id = test.id
LEFT OUTER JOIN test1 on test1.id = test.id
WHERE test_children.id = 1
How can I accomplish this? Also, the join type doesn't matter, they could
be inner joins too, the important thing is getting rid of the subquery
because it's completely locking up our database.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/YMD7zwrqODQJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, create_session, joinedload
e = create_engine('sqlite:////tmp/foo.db', echo=True)
Base = declarative_base(bind=e)
class Test(Base):
__tablename__ = 'test'
id = Column(Integer, primary_key=True)
type = Column(String(12))
name = Column(String(50))
__mapper_args__ = {
'polymorphic_identity': 'base',
'polymorphic_on': type,
'with_polymorphic': '*',
}
class TestOne(Test):
__tablename__ = 'test1'
id = Column(Integer, ForeignKey(Test.id), primary_key=True)
value1 = Column(String(16))
__mapper_args__ = {
'polymorphic_identity': 'one',
}
class TestChild(Base):
__tablename__ = 'test_children'
id = Column(Integer, primary_key=True)
test_id = Column(Integer, ForeignKey(Test.id))
parent = relation(Test, lazy=True, uselist=False)
if __name__ == '__main__':
Base.metadata.drop_all()
Base.metadata.create_all()
session = create_session(bind=e, autocommit=False)
query = session.query(TestChild) \
.options(joinedload(TestChild.parent)) \
.get(1)
