union with two different orders

[naktinis]

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()

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.

[Adrian von Bidder]

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.

[naktinis]

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.

[Michael Bayer]

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.

[Adrian von Bidder]

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

[Michael Bayer]

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)

[naktinis]

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.