[sqlalchemy] Re: Somewhat complex union_all() question
Ok Mike, Tell me what you think about this: q1 = DBSession.query(P1.id, P1.user_id, P1.type, P1.title, P1.body, P1.created, P1.updated) q2 = DBSession.query(P2.id, P2.user_id, 'P2', P2.title, P2.body, P2.created, P2.updated) q3 = DBSession.query(P3.id, P3.user_id, 'P3', P3.title, P3.body, P3.created, P3.updated) subquery = DBSession.query().from_statement(union_all(q1, q2, q3)).subquery() posts = DBSession.query(subquery, User.name).filter (User.user_id==subquery.c.user_id) Kind of hackish, but... it seems to work? Seth On Aug 28, 12:07 pm, Mike Conley mconl...@gmail.com wrote: 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. --~--~-~--~~~---~--~~ 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: Somewhat complex union_all() question
Nothing hackish about it. The SQL is doing exactly what you want; union the posting tables and join the result to users. Simple enough that the database engine should construct a reasonable plan. -- Mike Conley On Wed, Sep 9, 2009 at 8:41 PM, Seth seedifferen...@gmail.com wrote: Ok Mike, Tell me what you think about this: q1 = DBSession.query(P1.id, P1.user_id, P1.type, P1.title, P1.body, P1.created, P1.updated) q2 = DBSession.query(P2.id, P2.user_id, 'P2', P2.title, P2.body, P2.created, P2.updated) q3 = DBSession.query(P3.id, P3.user_id, 'P3', P3.title, P3.body, P3.created, P3.updated) subquery = DBSession.query().from_statement(union_all(q1, q2, q3)).subquery() posts = DBSession.query(subquery, User.name).filter (User.user_id==subquery.c.user_id) Kind of hackish, but... it seems to work? Seth --~--~-~--~~~---~--~~ 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: Somewhat complex union_all() question
On Sep 9, 2009, at 8:41 PM, Seth wrote: Ok Mike, Tell me what you think about this: q1 = DBSession.query(P1.id, P1.user_id, P1.type, P1.title, P1.body, P1.created, P1.updated) q2 = DBSession.query(P2.id, P2.user_id, 'P2', P2.title, P2.body, P2.created, P2.updated) q3 = DBSession.query(P3.id, P3.user_id, 'P3', P3.title, P3.body, P3.created, P3.updated) subquery = DBSession.query().from_statement(union_all(q1, q2, q3)).subquery() posts = DBSession.query(subquery, User.name).filter (User.user_id==subquery.c.user_id) Kind of hackish, but... it seems to work? you should be able to call select() directly on the union_all() and send that as your subquery. --~--~-~--~~~---~--~~ 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: Somewhat complex union_all() question
On Wed, Sep 9, 2009 at 10:46 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Sep 9, 2009, at 8:41 PM, Seth wrote: Ok Mike, Tell me what you think about this: q1 = DBSession.query(P1.id, P1.user_id, P1.type, P1.title, P1.body, P1.created, P1.updated) q2 = DBSession.query(P2.id, P2.user_id, 'P2', P2.title, P2.body, P2.created, P2.updated) q3 = DBSession.query(P3.id, P3.user_id, 'P3', P3.title, P3.body, P3.created, P3.updated) subquery = DBSession.query().from_statement(union_all(q1, q2, q3)).subquery() posts = DBSession.query(subquery, User.name).filter (User.user_id==subquery.c.user_id) Kind of hackish, but... it seems to work? you should be able to call select() directly on the union_all() and send that as your subquery. What would that look like? I don't get it the syntax. This is also really close to answering the question I posted earlier about labeling literals in the first query of a union. I'll reply to that thread again when I have the answer completed. -- 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Somewhat complex union_all() question
here is the sample code I am using http://pastebin.com/m6cd9c5dd -- Mike Conley On Wed, Sep 9, 2009 at 11:10 PM, Mike Conley mconl...@gmail.com wrote: On Wed, Sep 9, 2009 at 10:46 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Sep 9, 2009, at 8:41 PM, Seth wrote: Ok Mike, Tell me what you think about this: q1 = DBSession.query(P1.id, P1.user_id, P1.type, P1.title, P1.body, P1.created, P1.updated) q2 = DBSession.query(P2.id, P2.user_id, 'P2', P2.title, P2.body, P2.created, P2.updated) q3 = DBSession.query(P3.id, P3.user_id, 'P3', P3.title, P3.body, P3.created, P3.updated) subquery = DBSession.query().from_statement(union_all(q1, q2, q3)).subquery() posts = DBSession.query(subquery, User.name).filter (User.user_id==subquery.c.user_id) Kind of hackish, but... it seems to work? you should be able to call select() directly on the union_all() and send that as your subquery. What would that look like? I don't get it the syntax. This is also really close to answering the question I posted earlier about labeling literals in the first query of a union. I'll reply to that thread again when I have the answer completed. -- 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Somewhat complex union_all() question
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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Somewhat complex union_all() question
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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Somewhat complex union_all() question
Mike, Very helpful! Seems to work great. Wanna give me a tip on how to make the user's table join to this query via the user_id so that I can access the user's information at something like q.user.email? Seth On Aug 26, 9:22 pm, Mike Conley mconl...@gmail.com wrote: or should be a little better q=q1.union_all(q2,q3) -- Mike Conley On Thu, Aug 27, 2009 at 12:14 AM, Mike Conley mconl...@gmail.com wrote: Did you try something like this? 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) q=q1.union_all(q2).union_all(q3) -- Mike Conley On Wed, Aug 26, 2009 at 10:45 PM, Seth seedifferen...@gmail.com wrote: I have three different types of post tables with all of the same columns except that the first table has an extra column named type (placed in-between the 'user_id' and 'title' columns of the other tables). I want to do a UNION ALL that will combine the data from all these tables into a single list (with a blank value for the tables missing the type column), and then I want to sort the posts by creation date. However, try as I might, I cannot seem to get this to work without throwing me an ArgumentError of All selectables passed to CompoundSelect must have identical numbers of columns; select #1 has 7 columns, select #2 has 6. I suspect this has something to do with SQLAlchemy auto-magically reading the DeclarativeBase table classes. The SQL version of this query would look something like: SELECT 'cat1' as category, * FROM posts_1 UNION ALL SELECT 'cat2', id, user_id, 'NONE', title, body, created, updated FROM posts_2 UNION ALL SELECT 'cat3', id, user_id, 'NONE', title, body, created, updated FROM posts3 ORDER BY created DESC LIMIT 10 How can I get this to translate into SQLAlchemy with the extra 'NONE' for the tables that don't have a type column without giving me the ArgumentError? Thanks, Seth --~--~-~--~~~---~--~~ 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: Somewhat complex union_all() question
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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Somewhat complex union_all() question
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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Somewhat complex union_all() question
Did you try something like this? 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) q=q1.union_all(q2).union_all(q3) -- Mike Conley On Wed, Aug 26, 2009 at 10:45 PM, Seth seedifferen...@gmail.com wrote: I have three different types of post tables with all of the same columns except that the first table has an extra column named type (placed in-between the 'user_id' and 'title' columns of the other tables). I want to do a UNION ALL that will combine the data from all these tables into a single list (with a blank value for the tables missing the type column), and then I want to sort the posts by creation date. However, try as I might, I cannot seem to get this to work without throwing me an ArgumentError of All selectables passed to CompoundSelect must have identical numbers of columns; select #1 has 7 columns, select #2 has 6. I suspect this has something to do with SQLAlchemy auto-magically reading the DeclarativeBase table classes. The SQL version of this query would look something like: SELECT 'cat1' as category, * FROM posts_1 UNION ALL SELECT 'cat2', id, user_id, 'NONE', title, body, created, updated FROM posts_2 UNION ALL SELECT 'cat3', id, user_id, 'NONE', title, body, created, updated FROM posts3 ORDER BY created DESC LIMIT 10 How can I get this to translate into SQLAlchemy with the extra 'NONE' for the tables that don't have a type column without giving me the ArgumentError? Thanks, Seth --~--~-~--~~~---~--~~ 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: Somewhat complex union_all() question
or should be a little better q=q1.union_all(q2,q3) -- Mike Conley On Thu, Aug 27, 2009 at 12:14 AM, Mike Conley mconl...@gmail.com wrote: Did you try something like this? 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) q=q1.union_all(q2).union_all(q3) -- Mike Conley On Wed, Aug 26, 2009 at 10:45 PM, Seth seedifferen...@gmail.com wrote: I have three different types of post tables with all of the same columns except that the first table has an extra column named type (placed in-between the 'user_id' and 'title' columns of the other tables). I want to do a UNION ALL that will combine the data from all these tables into a single list (with a blank value for the tables missing the type column), and then I want to sort the posts by creation date. However, try as I might, I cannot seem to get this to work without throwing me an ArgumentError of All selectables passed to CompoundSelect must have identical numbers of columns; select #1 has 7 columns, select #2 has 6. I suspect this has something to do with SQLAlchemy auto-magically reading the DeclarativeBase table classes. The SQL version of this query would look something like: SELECT 'cat1' as category, * FROM posts_1 UNION ALL SELECT 'cat2', id, user_id, 'NONE', title, body, created, updated FROM posts_2 UNION ALL SELECT 'cat3', id, user_id, 'NONE', title, body, created, updated FROM posts3 ORDER BY created DESC LIMIT 10 How can I get this to translate into SQLAlchemy with the extra 'NONE' for the tables that don't have a type column without giving me the ArgumentError? Thanks, Seth --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
