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 [email protected].
To post to this group, send email to [email protected].
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
"""
