Re: [sqlalchemy] Re: Outer joins?

[Alex Hall]

That would be the simplest. Having something so inefficient just bugs me. :)

I'm using MSSQL, so limit() works. Would yield_per() help here, or is
that for something different? Even if it didn't help local memory, but
just kept the load on the DB server down, that would be good.

On 3/16/16, Christopher Lee  wrote:
> It sounds like you should just fire it up with the outer joins and watch
> memory on the box.  If it gets too high, or crashes entirely, then you can
> look into different approaches.  For example, you could keep the outer
> joins, but paginate your query so that it is only pulling a subset of the
> rows from your main table (but fully joining against the secondary
> tables).  Just one caveat... if you are using MySQL, then LIMIT and OFFSET
> are not your friends; you'll want to find a different pagination mechanism.
>
> On Wed, Mar 16, 2016 at 10:29 AM, Jonathan Vanasco 
> wrote:
>
>> We all inherit less-than-ideal situations.
>>
>> If this is running once a day and isn't impacting performance or other
>> work, I wouldn't really worry about the huge join matrix.  It sounds like
>> the current solution is "good enough".  In a few weeks or months you'll
>> be
>> better acquainted with SqlAlchemy and Sql in general and can revisit.
>>
>> In terms of your 15minute script: When you can use subqueries, filters
>> and
>> `load_only` for certain columns, your backend will generate a smaller
>> matrix and there will be a less data transferred "over the wire".
>>
>> --
>> 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 sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Outer joins?

[Jonathan Vanasco]

We all inherit less-than-ideal situations.

If this is running once a day and isn't impacting performance or other 
work, I wouldn't really worry about the huge join matrix.  It sounds like 
the current solution is "good enough".  In a few weeks or months you'll be 
better acquainted with SqlAlchemy and Sql in general and can revisit.

In terms of your 15minute script: When you can use subqueries, filters and 
`load_only` for certain columns, your backend will generate a smaller 
matrix and there will be a less data transferred "over the wire".  

-- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Outer joins?

[Alex Hall]

> On Mar 16, 2016, at 03:23, Jonathan Vanasco  wrote:
> 
> The database design you have is less than perfect.

I didn't make it, I came in long after it had been set up and now have to work 
with it. I can't re-do anything. They did it this way so that, for instance, a 
single attribute or attachment could apply to multiple items. If a thousand 
items are "large", and ten thousand have a "size" attribute, "size" and 'large" 
can both be written once instead of thousands of times. The problem is that it 
makes this hard to query, at least for someone not very experienced in DBA.

> The goal of having to reformat the relational DB into a CSV is less than 
> perfect.

The CSV is to give product details to our resellers in a format they can import 
automatically. As you say, flattening a relational database into what is 
essentially a single table isn't ideal, but it's what my work has always done 
so it's what I have to do. We have this running as a very convoluted SQL job I 
didn't write, with a ton of temporary tables, repeated code, and other fun 
things that make it hard to figure out what's going on. I know Python better 
than SQL, even if I have to learn SA and some DB concepts along the way, so 
this will be far easier to maintain once I get it working.

> 
> If I were you, I would think about 3 questions:
> 
> 1. How often do you have to run this?
Once a day or less. There's another script I'll eventually have to write that 
runs every fifteen minutes, but it has far less columns. Still, I'll meet to be 
able to grab items that lack related information, and the related information 
for items that have it.

> 2. Does it take too long?
No, I don't think it will. Even with the query that fails to get all items, it 
only takes 30 seconds total. I'm okay with it taking a few minutes.
> 3. Does it use up too much DB/Python memory?
That I don't know.
> 
> If this isn't a resource issue, and a 1x a day task... don't worry about it.  
> Let the non-optimal code run.
> 
> If you need to run this every 5 minutes, then I'd start to worry.
> 
> -- 
> 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 sqlalchemy+unsubscr...@googlegroups.com 
> .
> To post to this group, send email to sqlalchemy@googlegroups.com 
> .
> Visit this group at https://groups.google.com/group/sqlalchemy 
> .
> For more options, visit https://groups.google.com/d/optout 
> .

-- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Outer joins?

[Jonathan Vanasco]

The database design you have is less than perfect.
The goal of having to reformat the relational DB into a CSV is less than 
perfect.

If I were you, I would think about 3 questions:

1. How often do you have to run this?
2. Does it take too long?
3. Does it use up too much DB/Python memory?

If this isn't a resource issue, and a 1x a day task... don't worry about 
it.  Let the non-optimal code run.

If you need to run this every 5 minutes, then I'd start to worry.

-- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Outer joins?

[Alex Hall]

Thanks guys. I'm using automap, but I'm not completely sure how much
that gives me for free. Yes, these tables are big, and the resulting
set would be worrying large (potentially 5*20, and that's without
the attributes and attachments, plus their assignment and values
tables). I've switched to left outerjoins, hoping that that will keep
things smaller.

My original query is below. This works fine, but only gets items that
have vendor, attributes, and attachments. Items may have some or none
of these, as I was just informed today. As you can probably see,
item.itm_id ties everything together. It's used as the key for
assignmentTable, which holds all the attachments associated with the
item and uses att_id to index into attachmentTable and
attachmentTextTable. A similar relationship exists for attributes.

old_items = session.query(itemTable, attachmentTable,
attachmentTextTable, assignmentTable, attributeTable,
attributeValueTable, attributeValueAssignmentTable, vendorTable)\
.filter(vendorTable.PVVNNO == itemTable.itm_vendornum)\
.filter(assignmentTable.itm_id == itemTable.itm_id)\
.filter(assignmentTable.att_id == attachmentTable.att_id)\
.filter(assignmentTable.att_id == attachmentTextTable.att_id)\
.filter(attributeValueAssignmentTable.itm_id == itemTable.itm_id)\
.filter(attributeTable.attr_id == attributeValueAssignmentTable.attr_id)\
.filter(attributeValueTable.attr_value_id ==
attributeValueAssignmentTable.attr_value_id)

My next thought was to break this down into multiple queries:

allItems = session.query(items)\
.filter(items.itm_webflag != 'N', items.itm_suspflag != 'Y')

itemVendors = allItems.query(vendorTable).filter(vendorTable.PVVNNO ==
itemTable.itm_vendornum)

attachments = allItems.query(assignmentTable, attachmentTable,
attachmentTextTable)\
.filter(assignmentTable.itm_id == itemTable.itm_id)\
.filter(assignmentTable.att_id == attachmentTable.att_id)\
.filter(assignmentTable.att_id == attachmentTextTable.att_id)\

attributes = allItems.query(attributeTable, attributeValueTable,
attributeValueAssignmentTable)\
.filter(attributeValueAssignmentTable.itm_id == itemTable.itm_id)\
.filter(attributeTable.attr_id == attributeValueAssignmentTable.attr_id)\
.filter(attributeValueTable.attr_value_id ==
attributeValueAssignmentTable.attr_value_id)

The problem was, I couldn't work out how to put them together. Given
some item ID, how would I access that item's attributes or attachments
without making tons of queries back to the database?

Currently, I'm using this query, which I haven't yet even tested:

items = session.query(itemTable)\
.outerjoin(vendorTable, vendorTable.PVVNNO == itemTable.itm_vendornum)\
.outerjoin(assignmentTable, assignmentTable.itm_id == itemTable.itm_id)\
.filter(assignmentTable.att_id == attachmentTable.att_id)\
.outerjoin(attachmentTextTable, assignmentTable.att_id ==
attachmentTextTable.att_id)\
.outerjoin(attributeValueAssignmentTable,
attributeValueAssignmentTable.itm_id == itemTable.itm_id)\
.outerjoin(attributeTable, attributeTable.attr_id ==
attributeValueAssignmentTable.attr_id)\
.filter(attributeValueTable.attr_value_id ==
attributeValueAssignmentTable.attr_value_id)


On 3/15/16, Christopher Lee  wrote:
> Note that if your items have a lot of attributes and attachments, an
> outer-join will return a multiplicatively-large result set.  That will get
> boiled down into a sane number of objects by the SqlAlchemy ORM, but your
> performance might be ugly in terms of I/O to your database, or the
> processing time it takes to allocate the entire result set.  If the related
> tables are small, then querying all the data in a single query can be a lot
> faster.
>
> Anyway, it would help to see some code and know if you are using just the
> Core, or if you are using the ORM and have relationships defined.  You can
> pretty easily force an outer join on a relationship by setting the eager
> loading argument to "joined".  If you are using queries directly, then
> Jonathan's suggestions above should get you where you need to go.
>
>
>
> On Tue, Mar 15, 2016 at 10:17 AM, Jonathan Vanasco 
> wrote:
>
>> The ORM has an `outerjoin` method on queries:
>>
>> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.outerjoin
>>
>> You can also pass "isouter=True" to `join`
>>
>> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join
>>
>> The core supports an outerjoin in both variations as well:
>>
>> http://docs.sqlalchemy.org/en/latest/core/selectable.html?highlight=outer#sqlalchemy.sql.expression.Join.join
>>
>> http://docs.sqlalchemy.org/en/latest/core/selectable.html?highlight=outer#sqlalchemy.sql.expression.Join.outerjoin
>>
>> --
>> 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 sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this 

Re: [sqlalchemy] Re: Outer joins?

[Christopher Lee]

Note that if your items have a lot of attributes and attachments, an
outer-join will return a multiplicatively-large result set.  That will get
boiled down into a sane number of objects by the SqlAlchemy ORM, but your
performance might be ugly in terms of I/O to your database, or the
processing time it takes to allocate the entire result set.  If the related
tables are small, then querying all the data in a single query can be a lot
faster.

Anyway, it would help to see some code and know if you are using just the
Core, or if you are using the ORM and have relationships defined.  You can
pretty easily force an outer join on a relationship by setting the eager
loading argument to "joined".  If you are using queries directly, then
Jonathan's suggestions above should get you where you need to go.



On Tue, Mar 15, 2016 at 10:17 AM, Jonathan Vanasco 
wrote:

> The ORM has an `outerjoin` method on queries:
>
> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.outerjoin
>
> You can also pass "isouter=True" to `join`
>
> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join
>
> The core supports an outerjoin in both variations as well:
>
> http://docs.sqlalchemy.org/en/latest/core/selectable.html?highlight=outer#sqlalchemy.sql.expression.Join.join
>
> http://docs.sqlalchemy.org/en/latest/core/selectable.html?highlight=outer#sqlalchemy.sql.expression.Join.outerjoin
>
> --
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Outer joins?

[Jonathan Vanasco]

The ORM has an `outerjoin` method on queries:

http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.outerjoin

You can also pass "isouter=True" to `join` 
  
  
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join

The core supports an outerjoin in both variations as well:

http://docs.sqlalchemy.org/en/latest/core/selectable.html?highlight=outer#sqlalchemy.sql.expression.Join.join
  
  
http://docs.sqlalchemy.org/en/latest/core/selectable.html?highlight=outer#sqlalchemy.sql.expression.Join.outerjoin

-- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Outer joins with ORM and single table polymorphism

[MikeCo]

After some experimenting I am able to generate the correct query
by two methods
(1) build query from ORM classes with ORM session.query()
(2) build query from underlying tables with sql expressions

I like the ORM based method better, because the code does not need
to know which columns are involved in the join, and does seem to be
a little cleaner.
I do have a couple of questions about this though:
(1) I can't figure out how to access columns from the RowTuple result
by name. I can only get values by absolute index row[0],row[1], etc.
(2) Are subquery aliases always anonymous or can I control the name?

The table based query satisfies all requirements for getting data. The
only issue is that the code now has more embedded information about
the
relations between tables.


Generated SQL here
 http://dpaste.com/hold/116982/
Here is a complete test program; I am using version 0.5.2
 http://dpaste.com/hold/116995/

 Mapped classes based query
# set up subqueries for outer joins
S1 = session.query(SRC1).subquery()
S2 = session.query(SRC2).subquery()
S3 = session.query(SRC3).subquery()
# build up the query
query = session.query(Job.title, Step.name).join(Step)
query = query.outerjoin([S1,S2,S3])
query = query.add_column(S1.c.value)
query = query.add_column(S2.c.value)
query = query.add_column(S3.c.value)
query = query.with_labels()
# select relevant job
query = query.filter(Job.id==2)
for r in query:
print r  # how to get named access to columns? r[0],r[1],... works
 # r.job_title, etc. doesn't

 Table based query
# get access to underlying tables
J = class_mapper(Job).mapped_table
S = class_mapper(Step).mapped_table
I = class_mapper(Input).mapped_table
# set up some alias names for outerjoins
S1 = select([I], I.c.kind=='SRC1')\
.with_only_columns([I.c.step_id,I.c.value]).alias('S1')
S2 = select([I], I.c.kind=='SRC2')\
.with_only_columns([I.c.step_id,I.c.value]).alias('S2')
S3 = select([I], I.c.kind=='SRC3')\
.with_only_columns([I.c.step_id,I.c.value]).alias('S3')
# build a query
query = J.join(S)
query = query.outerjoin(S1, S.c.id==S1.c.step_id)
query = query.outerjoin(S2, S.c.id==S2.c.step_id)
query = query.outerjoin(S3, S.c.id==S3.c.step_id)
query = query.select(use_labels=True)
query = query.with_only_columns([J.c.title,S.c.name,
S1.c.value,S2.c.value,S3.c.value])
# select rows of interest
query = query.where(J.c.id==1)
for r in conn.execute(query):
print r.job_title, r.step_name, r.S1_value, r.S2_value, r.S3_value

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Outer joins with ORM and single table polymorphism

[Michael Bayer]

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] Re: Outer joins with ORM and single table polymorphism

[MikeCo]

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
-~--~~~~--~~--~--~---