I have a user object, and an extended user object which inherits from the base user object. I also have a test object, which has a FK to the extended user object. When I try this:
res = session.query(Test) \ .options(joinedload('user')) \ .all() I see this sql generated: SELECT test.id AS test_id, test.user_id AS test_user_id, anon_1.users_ext_id AS anon_1_users_ext_id, anon_1.users_id AS anon_1_users_id FROM test LEFT OUTER JOIN (SELECT users.id AS users_id, users_ext.id AS users_ext_id FROM users JOIN users_ext ON users.id = users_ext.id) AS anon_1 ON anon_1.users_ext_id = test.user_id there are no limits or constraints on the subquery, so this takes a nightmarish amount of time to resolve (hundreds of thousands of users in the table), while the query i was expecting works instantly. The query I am trying to achieve is: SELECT test.id AS test_id, test.user_id AS test_user_id, anon_1.users_ext_id AS anon_1_users_ext_id, anon_1.users_id AS anon_1_users_id FROM test LEFT OUTER JOIN users_ext ON users_ext.id = test.user_id JOIN users ON users_ext.id = users.id How can I generate the second query instead of the first? It seems like all the parts are there, but put together in a terrible way that ensures it will take the maximum amount of time to resolve. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
from sqlalchemy import * from sqlalchemy.orm import relation, joinedload, create_session from sqlalchemy.ext.declarative import declarative_base e = create_engine('sqlite:////tmp/foo.db', echo=True) Base = declarative_base(bind=e) class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) class UserExtension(User): __tablename__ = 'users_ext' id = Column(Integer, ForeignKey(User.id), primary_key=True) class Test(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) user_id = Column(Integer, ForeignKey(UserExtension.id)) user = relation(UserExtension, lazy=True) Base.metadata.drop_all() Base.metadata.create_all() session = create_session(bind=e) res = session.query(Test) \ .options(joinedload('user')) \ .all() """ SELECT test.id AS test_id, test.user_id AS test_user_id, anon_1.users_ext_id AS anon_1_users_ext_id, anon_1.users_id AS anon_1_users_id FROM test LEFT OUTER JOIN (SELECT users.id AS users_id, users_ext.id AS users_ext_id FROM users JOIN users_ext ON users.id = users_ext.id) AS anon_1 ON anon_1.users_ext_id = test.user_id """