Thanks; the monkeypatch approach works nicely. Using the alias()
method will raise
AttributeError: 'Alias' object has no attribute '_order_by_clause'
On Fri, Dec 5, 2008 at 7:25 PM, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
> there's logic which is removing the order_by's from the selects, and
> in that case this is what's blowing away the parenthesis as well.
> Some databases don't even allow ORDER BY inside of the queries used in
> a UNION since in the absense of LIMIT/OFFSET, which also is not
> standard SQL, they have no effect.
>
> However I dont think its good form for SQLA to be whacking the ORDER
> BY from the unions if that is in fact what was requested. So this
> behavior is changed in the 0.5 series in r5425. As far as the 0.4
> series, we're only supporting critical bugfixes there and I'd like to
> avoid any behavioral changes (0.4 is also on a more conservative
> release schedule). If you're truly stuck with 0.4, you can use
> select.order_by(...).alias().select() to get an equivalent query which
> is insulated from changes (and is probably more compatible across
> databases), or to get exactly the same SQL here's a safe monkeypatch
> approach:
>
> from sqlalchemy import *
> s = select(["x", "y"]).select_from("table")
>
> def frozen_order_by(s):
> s = s.self_group()
> s.order_by = lambda *args: s
> return s
>
> qlist = [s.limit(10).order_by('x'), s.limit(10).order_by('x')]
> print union_all(*[frozen_order_by(q) for q in
> qlist]).order_by("foo").limit(10)
>
> frozen_order_by() calls self_group() thereby generating a new select()
> so that the original is unchanged.
>
>
>
> On Dec 5, 2008, at 5:08 PM, Bo Shi wrote:
>
>>
>> Oh, check this out:
>>
>> (SA 0.4.7)
>>
>>>>> from sqlalchemy import *
>>>>> s = select(["x", "y"]).select_from("table")
>>>>> qlist = [s.limit(10).order_by('x').self_group(),
>>>>> s.limit(10).order_by('x').self_group()]
>>
>>>>> print union_all(*qlist).order_by("foo").limit(10)
>> SELECT x, y
>> FROM table
>> LIMIT 10 UNION ALL SELECT x, y
>> FROM table
>> LIMIT 10 ORDER BY foo
>> LIMIT 10
>>
>>>>> for q in qlist:
>> ... print q
>> ...
>> (SELECT x, y
>> FROM table ORDER BY x
>> LIMIT 10)
>> (SELECT x, y
>> FROM table ORDER BY x
>> LIMIT 10)
>>
>>
>>
>> On Fri, Dec 5, 2008 at 4:55 PM, Bo Shi <[EMAIL PROTECTED]> wrote:
>>> I had to upgrade to 0.4.7 from 0.4.2, but your sample query works,
>>> however, my application of it does not.
>>>
>>> Sorry I'm being so light on details, I'll try to reproduce with a
>>> complete sample versus using snippets of production code.
>>>
>>> Each select statement is generated like so:
>>>
>>> sel = select(pre_select + selectlist,
>>> from_obj=join_datatables(tables))
>>>
>>> I apply self_group() using a list comprehension and if I print each
>>> select statement in the list individually, the parentheses show up.
>>>
>>> If I then union_all(*querylist) and print that, the parentheses
>>> disappear.
>>>
>>> Weird.
>>>
>>> I should note that the individual selects have filters, an order by
>>> clause and a limit, but the following works fine so I would not
>>> expect
>>> that to be a problem.
>>>
>>>>>> from sqlalchemy import *
>>>>>> s = select(["x", "y"]).select_from("table")
>>>>>> print s.self_group()
>>> (SELECT x, y
>>> FROM table)
>>>>>> print union_all(s.self_group(), s.self_group()).order_by("foo")
>>> (SELECT x, y
>>> FROM table) UNION ALL (SELECT x, y
>>> FROM table) ORDER BY foo
>>>>>> print union_all(s.limit(10).self_group(),
>>>>>> s.limit(10).self_group()).order_by("foo").limit(10)
>>> (SELECT x, y
>>> FROM table
>>> LIMIT 10) UNION ALL (SELECT x, y
>>> FROM table
>>> LIMIT 10) ORDER BY foo
>>> LIMIT 10
>>>>>> import sqlalchemy as sa
>>>>>> sa.__version__
>>> '0.4.7'
>>>
>>>
>>> On Fri, Dec 5, 2008 at 4:23 PM, Michael Bayer <[EMAIL PROTECTED]
>>> > wrote:
>>>>
>>>>
>>>>>>> from sqlalchemy import *
>>>>>>> s = select(["x", "y"]).select_from("table")
>>>>>>> print union_all(s.self_group(), s.self_group()).order_by("foo")
>>>> (SELECT x, y
>>>> FROM table) UNION ALL (SELECT x, y
>>>> FROM table) ORDER BY foo
>>>>
>>>>
>>>> On Dec 5, 2008, at 4:17 PM, Bo Shi wrote:
>>>>
>>>>>
>>>>> Thanks for the quick response!
>>>>>
>>>>> The following does *not* work. Am I making the call incorrectly?
>>>>>
>>>>> sel = union_all(*[q.self_group() for q in querylist])
>>>>>
>>>>>
>>>>> On Fri, Dec 5, 2008 at 4:08 PM, Michael Bayer <[EMAIL PROTECTED]
>>>>>> wrote:
>>>>>>
>>>>>> try calling self_group() on each select object.
>>>>>>
>>>>>> On Dec 5, 2008, at 3:55 PM, Bo Shi wrote:
>>>>>>
>>>>>>>
>>>>>>> Hi all,
>>>>>>>
>>>>>>> There appear to be some nuances to using order by statements with
>>>>>>> set
>>>>>>> operations like unions in MySQL but the following is allowed*:
>>>>>>>
>>>>>>> (SELECT a,b from DBA.tbl ORDER BY b LIMIT 5)
>>>>>>> UNION ALL
>>>>>>> (SELECT a,b from DBB.tbl ORDER BY b LIMIT 5)
>>>>>>> ORDER BY b
>>>>>>>
>>>>>>> When I attempt to generate such a statement with:
>>>>>>>
>>>>>>> union_all(*list_of_select_objs),
>>>>>>>
>>>>>>> The SQL generated lacks parentheses around the SELECT
>>>>>>> statements (in
>>>>>>> addition to dropping the order by clauses, but that appears to be
>>>>>>> expected behavior). Is there a way to put the parentheses in?
>>>>>>>
>>>>>>>
>>>>>>> *just an example, the query i've written is meaningless/
>>>>>>> useless :-)
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Bo Shi
>>>>>>> 207-469-8264
>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Bo Shi
>>>>> 207-469-8264
>>>>>
>>>>>>
>>>>
>>>>
>>>>>>
>>>>
>>>
>>>
>>>
>>> --
>>> Bo Shi
>>> 207-469-8264
>>>
>>
>>
>>
>> --
>> Bo Shi
>> 207-469-8264
>>
>> >
>
>
> >
>
--
Bo Shi
207-469-8264
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
