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
>
> >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
