Right; my bad - I misread the instructions.
On Sat, Dec 6, 2008 at 9:41 AM, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
> you'd say, s.alias().select()
>
> it makes subqueries which MySQL probably doesn't require.
>
>
> On Dec 5, 2008, at 10:35 PM, Bo Shi wrote:
>
>>
>> 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
>>
>> >
>
>
> >
>
--
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
-~----------~----~----~----~------~----~------~--~---
