Mike, Thanks again for your posts. What about something like:
q1 = DBSession.query(P1.id, P1.user_id, P1.type, P1.title, P1.body, P1.created, P1.updated, User.name).filter(P1.user_id==User.id) q2 = DBSession.query(P2.id, P2.user_id, "'P2'", P2.title, P2.body, P2.created, P2.updated, User.name).filter(P2.user_id==User.id) q3 = DBSession.query(P3.id, P3.user_id, "'P3'", P3.title, P3.body, P3.created, P3.updated, User.name).filter(P3.user_id==User.id) posts = q1.union_all(q2, q3) ? Seth On Aug 27, 2:45 pm, Mike Conley <mconl...@gmail.com> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
