I can't figure out how to write this outer join query in ORM-speak.
Jobs have Steps; Steps have optional inputs of type "SRC1", "SRC2", or
"SRC3". Steps are stored in a single table with column "kind" as a
discriminator. The existing legacy code uses a SELECT statement with
outer joins to get the correct result.
Here are the classes
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(20))
jobnum = Column(Integer)
steps = relation('Step', backref=('jobs'))
def __repr__(s): return '<Job> %s %s'%(s.id,s.title)
class Step(Base):
__tablename__ = 'step'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(20))
job_id = Column(Integer, ForeignKey('job.id'))
inputs = relation('Input', backref=('step'))
def __repr__(s): return '<Step> %s %s'%(s.id,s.name)
class Input(Base):
__tablename__ = 'input'
id = Column(Integer, primary_key=True, autoincrement=True)
kind = Column(String(10))
value = Column(String(20))
step_id = Column(Integer, ForeignKey('step.id'))
__mapper_args__ = {'polymorphic_on': kind}
def __repr__(s): return '<Input> %s %s %s'%(s.id,s.kind,s.value)
class SRC1(Input):
__mapper_args__ = {'polymorphic_identity': 'SRC1'}
class SRC2(Input):
__mapper_args__ = {'polymorphic_identity': 'SRC2'}
class SRC3(Input):
__mapper_args__ = {'polymorphic_identity': 'SRC3'}
The legacy query works correctly in SA like this, including returning
"None" for missing Input columns
session.execute("""
select job.title, step.name, src1.value, src2.value, src3.value
FROM
job
JOIN step ON step.job_id = job.id
LEFT OUTER JOIN
(SELECT step.id AS id, step.name AS name, input.value AS value
FROM step
JOIN input ON step.id = input.step_id
WHERE input.kind = 'SRC1') AS src1
ON src1.id = step.id
LEFT OUTER JOIN
(SELECT step.id AS id, step.name AS name, input.value AS value
FROM step
JOIN input ON step.id = input.step_id
WHERE input.kind = 'SRC2') AS src2
ON src2.id = step.id
LEFT OUTER JOIN
(SELECT step.id AS id, step.name AS name, input.value AS value
FROM step
JOIN input ON step.id = input.step_id
WHERE input.kind = 'SRC3') AS src3
ON src3.id = step.id
""")
How do I do this with ORM queries?
I have tried variations of query.outerjoin, without success. I think
some form of this should work, but the generated SQL isn't right
q = session.query
(Job.title,Step.name,SRC1.value,SRC2.value,SRC3.value)
q = q.join(Step)
q = q.outerjoin(SRC1, aliased=True)
q = q.outerjoin(SRC2, aliased=True)
q = q.outerjoin(SRC3, aliased=True)
q = q.filter(Job.id==1)
generated this SQL:
SELECT job.title AS job_title,
step.name AS step_name,
input.value AS input_value
FROM input,
job
JOIN step
ON job.id = step.job_id
LEFT OUTER JOIN INPUT AS input_1
ON step.id = input_1.step_id
LEFT OUTER JOIN INPUT AS input_2
ON step.id = input_2.step_id
LEFT OUTER JOIN INPUT AS input_3
ON step.id = input_3.step_id
WHERE job.id = ?
AND input.kind IN (?)
AND input.kind IN (?)
AND input.kind IN (?)
with these parameters
[1, 'SRC1', 'SRC2','SRC']
This is actually close, but
1. the three outer joins need to be aliased and qualified by SRCx
strings to reflect the polymorphism. the three input.kind IN clauses
are in the wrong place.
2. including 'input' in the FROM clause will cause a cross join if
the any data is actually retrieved
3. SELECT xxx should include 3 columns for the 3 SRCx.value columns
So, HELP, I obviously have a lot to learn how to construct more
complex queries.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
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
-~----------~----~----~----~------~----~------~--~---
