CODEWITHSUNDEEP
pythonsqlormsqlalchemy
Jerry
Jerry

Reputation: 2567

SQLAlchemy Generates Wrong SQL (for aliased and self-join)?

SQLAlchemy 0.8 is generating wrong SQL for the following simple self-join --

class ScheduledJob(Base):
    Id = Column('Id', Integer, primary_key=True)
    DependentJob1 = Column('DependentJob1', Integer)

DJ1 = aliased(ScheduledJob)
query = dbsession.query(ScheduledJob.Id, DJ1.Id).outerjoin(
        DJ1, ScheduledJob.DependentJob1==DJ1.Id)
print query

and prints the wrong SQL --

SELECT "ScheduledJob"."Id" AS "ScheduledJob_Id"
FROM "ScheduledJob" LEFT OUTER JOIN "ScheduledJob" AS "ScheduledJob_1" ON
"ScheduledJob"."DependentJob1" = "ScheduledJob"."Id"

while it should have been --

SELECT "ScheduledJob"."Id" AS "ScheduledJob_Id",
"ScheduledJob_1"."Id" AS "ScheduledJob_1_Id"
FROM "ScheduledJob" LEFT OUTER JOIN "ScheduledJob" AS "ScheduledJob_1" ON
"ScheduledJob"."DependentJob1" = "ScheduledJob_1"."Id"

What's more puzzling is that all the standalone aliased/join/self-join tests I tried following SQLAlchemy document all work fine.

What could have gone wrong? Any pointer will be greatly appreciated.

BTW, I'm mapping multiple databases, could it be related to this problem?

Upvotes: 1

Views: 565

Answers (2)

Jerry
Jerry

Reputation: 2567

The problem was caused by incorrect usage of SQLAlchemy, which mapped multiple tables extending from a same Base and with the same __tablename__

Everything works fine once I separated them to different Base --

class ScheduledJobMixin(object):
    Id = Column('Id', Integer, primary_key=True)
    ...

Remote_Base = declarative_base()
class RemoteScheduledJob(ScheduledJobMixin, Remote_Base):
    __tablename__ = 'ScheduledJob'

Base = declarative_base()
class ScheduledJob(ScheduledJobMixin, Base):
    __tablename__ = 'ScheduledJob'

Upvotes: 1

zzzeek
zzzeek

Reputation: 75137

cannot reproduce. test case run on 0.8.2, 0.8.1, 0.8.0, results are correct:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class ScheduledJob(Base):
    __tablename__ = 'ScheduledJob'

    Id = Column('Id', Integer, primary_key=True)
    DependentJob1 = Column('DependentJob1', Integer)

session = Session()

DJ1 = aliased(ScheduledJob)
query = session.query(ScheduledJob.Id, DJ1.Id).outerjoin(DJ1, ScheduledJob.DependentJob1==DJ1.Id)
print query

output:

SELECT "ScheduledJob"."Id" AS "ScheduledJob_Id", "ScheduledJob_1"."Id" AS "ScheduledJob_1_Id" 
FROM "ScheduledJob" LEFT OUTER JOIN "ScheduledJob" AS "ScheduledJob_1" ON "ScheduledJob"."DependentJob1" = "ScheduledJob_1"."Id"

I suspect that in your actual application, "DJ1" is not actually an alias of ScheduledJob.

Upvotes: 1

Related Questions