It works and will probably be OK. Using this style (query for user multiple times) in a really big database could lead to a performance problem depending on how the underlying database engine constructs a query plan. Try it and see how it goes.
-- Mike Conley On Fri, Aug 28, 2009 at 2:22 PM, Seth <seedifferen...@gmail.com> wrote: > > 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 -~----------~----~----~----~------~----~------~--~---
