On Oct 15, 2010, at 7:43 AM, Sebastian Elsner wrote:
> Hello,
>
> wanting to create the following query:
>
> (SELECT * from tbl ORDER BY b LIMIT 5) UNION ALL (SELECT * from tbl ORDER BY
> a LIMIT 5)
First note that ORDER BY is not valid at the level you are placing it (many
databases won't accept it, I am recalling that MySQL makes an exception when
LIMIT is present).
Second note that self_group() is a member of ClauseElement and has never been
available on Query.
Third, you don't want to q.union_all(q1, q2) - that will create three queries
in your union, not two, i.e. "select * from table UNION ALL select* from
table UNION ALL select * from table". Here you'd be looking for
q1.union_all(q2, q3, ...), except that isn't going to group the way you want.
Some DBs in fact don't accept () around UNION elements so we can't deploy
parens like that generically.
The most ideal way to get a relationally correct statement would be:
from sqlachemy import union_all
q.from_statement(
union_all(
q.order_by(t.name).limit(5).from_self(),
q.order_by(t.name).limit(5).from_self()
)
)
the self_group(), which isn't backend-neutral, would be:
print q.from_statement(
union_all(
q.order_by(t.name).limit(5).statement.self_group(),
q.order_by(t.name).limit(5).statement.self_group()
)
)
>
> The important thing are the (), which I cant get to work with the examples in
> the union() docs .
>
> I came across a solution involving self_group on Queries in an old mailing
> list post (using SA 0.4)
>
> This is what I figured:
>
> q=session.query(Table)
> querylist=[q.order_by(Table.b).limit(5), q.order_by(Table.a).limit(5)]
>
> sel = q.union_all(*[q.self_group() for q in querylist])
>
> But I get an error, that Query has not self_group attribute.
>
> Seems self_group is not longer a Query member. So how would I create a query
> like this?
>
> cheers
>
> Sebastian
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@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.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
