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 <[email protected]> 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 [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
