OK, I can mostly answer my own question q1=session.query(P1.userid,P1.extra,P1.title,P1.body) q2=session.query(P2.userid,"'X'",P2.title,P2.body) q3=session.query(P3.userid,"'X'",P3.title,P3.body) subq=q1.union_all(q2,q3).subquery() q = session.query(USER.email, subq).join((subq, USER.userid==subq.c.userid))
gives the desired SQL But what if I have a real requirement to retrieve the email address last in the row? q = session.query(subq,USER.email).join((USER, USER.userid==subq.c.userid)) and q = session.query(subq,USER.email).join((subq, USER.userid==subq.c.userid)) both complain AttributeError: 'NoneType' object has no attribute 'base_mapper' Probably because the subq is first in the list and is not an entity -- Mike Conley On Thu, Aug 27, 2009 at 5:05 PM, Mike Conley <mconl...@gmail.com> wrote: > Assuming a declarative based class USER exists, then you can join each of > the queries q1, q2, q3 to USER like this: > > q1 = session.query(P1.userid,P1.extra,P1.title,P1.body,USER.email) > q1 = q1.join((USER,USER.userid==P1.userid)) > q2 = session.query(P2.userid,"'X'",P2.title,P2.body,USER.email) > q2 = q2.join((USER,USER.userid==P2.userid)) > q3 = session.query(P3.userid,"'X'",P3.title,P3.body,USER.email) > q3 = q3.join((USER,USER.userid==P3.userid)) > > q=q1.union_all(q2,q3) > > Not a very elegant solution, and probably leads to an inefficient query > plan in many databases. > > Can anyone tell us how to join the result of union_all with another table? > Probably a subquery()? > > Effectively: > - create q1, q2, q3 as selects from P1, P2, P# as in original solution > - combine q1, q2, q3 with a union_all() > - add column USER.email to the query > - join resulting query to USER based on userid column in the union_all > statement > > SQL would look something like this: > > SELECT qry.a, qry.b, qry.c, user.x > FROM (SELECT a,b,c FROM p1 > UNION ALL SELECT a,b,c FROM p2 > UNION ALL SELECT a,b,c FROM p3) as qry > JOIN USER on qry.a = USER.a > > but I can't seem to get this result in SQLAlchemy > > > -- > Mike Conley > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
