outerjoin() will bridge between multiple tables if you specify a list
to a single outerjoin() call (see the examples in the reference
documentation). However for joined table inheritance with the ORM no
explicit joining is necessary, see the documenation on using
with_polymorphic in the mapper inheritance section. If you do want
to join explciitly using mappers that are already joined by
inheritance, it may be necessary to join with the mapped Table objects
directly (assuming you're on 0.5) since the mapper SRC1, SRC2, etc.
already indicate "the join of the base table to the SRC table", etc.
On Feb 1, 2009, at 11:04 PM, MikeCo wrote:
>
> Oops, the description should say
> Inputs are stored in a single table with column "kind" as a
> discriminator.
>
> On Feb 1, 10:58 pm, MikeCo <mconl...@gmail.com> wrote:
>> 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
-~----------~----~----~----~------~----~------~--~---
