[sqlalchemy] Re: union with two different orders
naktinis wrote: I tried calling .subquery() method on each union subquery like this: q1 = Thing.query().filter(...).order_by(Thing.a.desc()).limit (1).subquery() q2 = Thing.query().filter(...).order_by(Thing.a.asc()).limit (1).subquery() q = q1.union(q2).order_by(Thing.id) I know you're not doing that since the alias object returned by subquery() does not have a union() method. you'll have to wrap each subquery in a SELECT like this: q1 = sess.query(C).order_by(C.data).limit(2).subquery().select() q2 = sess.query(C).order_by(C.data.desc()).limit(2).subquery().select() print sess.query(C).select_from(union(q1, q2)).order_by(C.data) --~--~-~--~~~---~--~~ 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: union with two different orders
On 2 Rugs, 20:41, Michael Bayer mike...@zzzcomputing.com wrote: naktinis wrote: I tried calling .subquery() method on each union subquery like this: q1 = Thing.query().filter(...).order_by(Thing.a.desc()).limit (1).subquery() q2 = Thing.query().filter(...).order_by(Thing.a.asc()).limit (1).subquery() q = q1.union(q2).order_by(Thing.id) I know you're not doing that since the alias object returned by subquery() does not have a union() method. Sorry, you are right. I used union(q1, q2). Just copied the old source from previous post. you'll have to wrap each subquery in a SELECT like this: q1 = sess.query(C).order_by(C.data).limit(2).subquery().select() q2 = sess.query(C).order_by(C.data.desc()).limit(2).subquery().select() print sess.query(C).select_from(union(q1, q2)).order_by(C.data) Thanks, it works. --~--~-~--~~~---~--~~ 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: union with two different orders
On Saturday 06 June 2009 17.39:20 naktinis wrote: I think this was not the case, since I didn't expect the merged result to be ordered. To be more precise, the query looks like: q1 = Thing.query().filter(...).order_by(Thing.a.desc()).limit(1) q2 = Thing.query().filter(...).order_by(Thing.a.asc()).limit(1) q = q1.union(q2).order_by(Thing.id).all() The q1 returns first filtered element with largest 'a' column, q2 - first with smallest 'a'. So, I guess my question is still valid. You didn't mention limit in your first post, so I misunderstood what you were trying to do, sorry. Yes, as Michael said, subqueries are the way to go. I'm quite new to sa, so I can't help you there. cheers -- vbi -- featured link: http://www.pool.ntp.org signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: union with two different orders
On Saturday 06 June 2009 14.18:33 naktinis wrote: I want to use union on two queries, which have different order: q1 = Thing.query().order_by(Thing.a) q2 = Thing.query().order_by(Thing.b) q = q1.union(q2).all() SQL doesn't work as you think it does here. A UNION does not concatenate the results of the two queries, but is allowed to return the result in any order. ORDER BY can *then* be applied to the end result of your union. So even if you use subqueries, the order by in the subqueries might just be ignored. This is to allow the SQL query planner to be clever while building the union (perhaps a large union over two queries over the same table: if both queries require a table scan over the large table, the planner might decide to build the union by scanning the table only once while running both queries in parallel, so the table is loaded from disk once insead of twice. The UNION would then contain the resulting rows in more or less random order.) But I digress. What you want to do is something like: SELECT 1 as COL1, ... FROM ... UNION SELECT 2 as COL1, ... FROM ... ORDER BY COL1, ... cheers -- vbi But after this query I get MySQL error message Incorrect usage of UNION and ORDER BY. I guess that this could be because having SELECT ... UNION SELECT ... ORDER BY B, it is not clear whether the second subquery or both queries should be ordered using B criteria. I think this can be solved by adding brackets to each of the subquery: (SELECT ...) UNION (SELECT ...). Is there any way to create this query using SQLAlchemy ORM? I am using SQLAlchemy 0.5.4. -- Vertrauen ist gut. Anwalt ist saugeil. --~--~-~--~~~---~--~~ 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: union with two different orders
On Jun 6, 2009, at 11:39 AM, naktinis wrote: I think this was not the case, since I didn't expect the merged result to be ordered. To be more precise, the query looks like: q1 = Thing.query().filter(...).order_by(Thing.a.desc()).limit(1) q2 = Thing.query().filter(...).order_by(Thing.a.asc()).limit(1) q = q1.union(q2).order_by(Thing.id).all() The q1 returns first filtered element with largest 'a' column, q2 - first with smallest 'a'. So, I guess my question is still valid. if youre using limit with order by, you would have to wrap those queries within subqueries in order for UNION to accept them as encapsulated relations. On 6 Bir, 17:49, Adrian von Bidder avbid...@fortytwo.ch wrote: On Saturday 06 June 2009 14.18:33 naktinis wrote: I want to use union on two queries, which have different order: q1 = Thing.query().order_by(Thing.a) q2 = Thing.query().order_by(Thing.b) q = q1.union(q2).all() SQL doesn't work as you think it does here. A UNION does not concatenate the results of the two queries, but is allowed to return the result in any order. ORDER BY can *then* be applied to the end result of your union. So even if you use subqueries, the order by in the subqueries might just be ignored. This is to allow the SQL query planner to be clever while building the union (perhaps a large union over two queries over the same table: if both queries require a table scan over the large table, the planner might decide to build the union by scanning the table only once while running both queries in parallel, so the table is loaded from disk once insead of twice. The UNION would then contain the resulting rows in more or less random order.) But I digress. What you want to do is something like: SELECT 1 as COL1, ... FROM ... UNION SELECT 2 as COL1, ... FROM ... ORDER BY COL1, ... cheers -- vbi But after this query I get MySQL error message Incorrect usage of UNION and ORDER BY. I guess that this could be because having SELECT ... UNION SELECT ... ORDER BY B, it is not clear whether the second subquery or both queries should be ordered using B criteria. I think this can be solved by adding brackets to each of the subquery: (SELECT ...) UNION (SELECT ...). Is there any way to create this query using SQLAlchemy ORM? I am using SQLAlchemy 0.5.4. -- Vertrauen ist gut. Anwalt ist saugeil. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
