this is ticket 2188: http://www.sqlalchemy.org/trac/ticket/2188
The issue involves excessive digging into the column expressions of the order by, when it attempts to place those columns into the nested query. This step is necessary for many tests where the columns need to be SELECTed on the inside so that they can be ordered on the outside. A patch against 0.7 is attached. Once tests are assembled I'll patch it to both 0.6 and 0.7. Thanks for the clear test case !
On Jun 8, 2011, at 12:50 AM, Michael Bayer wrote:
> sorry, either:
>
> results = session.query(Book).options(
> orm.undefer(Book.num_purchased),
> orm.contains_eager('purchases')
> ).order_by("calculated_books_num_purchased").\
>
> limit(50).from_self().outerjoin(Book.purchases).all()
>
>
> or
>
> results = session.query(Book).options(
> orm.undefer(Book.num_purchased),
> orm.contains_eager('purchases')
> ).order_by(Book.num_purchased).\
>
> limit(50).from_self().outerjoin(Book.purchases).all()
>
>
> the latter is more guaranteed to work correctly though may be less efficient
> depending on backend.
>
>
>
> On Jun 8, 2011, at 12:47 AM, Michael Bayer wrote:
>
>> you can do it manually as the combination of automatic joins and ordering by
>> subqueries seems to get tripped up (its a bug, but not sure of the nature of
>> it)
>>
>> results = session.query(Book).options(
>> orm.undefer(Book.num_purchased),
>> orm.contains_eager('purchases')
>>
>> ).limit(50).from_self().outerjoin(Book.purchases).all()
>>
>>
>>
>>
>> On Jun 7, 2011, at 7:12 PM, Yoann Roman wrote:
>>
>>> I ran into a problem with a rather unique query on SA 0.6.5
>>> (reproducible on 0.7.1). The query eagerloads a collection, undefers a
>>> scalar-select column, orders on the latter, and applies a limit/
>>> offset. To order by a scalar-select column without executing the
>>> subquery again, I have to apply a custom label on the select and use
>>> that in the order_by. The problem is that, when SA nests the query
>>> because of the limit/eagerload combo, it tacks on the order_by columns
>>> again, which fails since the custom label isn't valid by itself.
>>>
>>> Here is a self-contained script to illustrate the problem:
>>> http://dl.dropbox.com/u/45702/deferred.py
>>>
>>> The problem, on SA 0.6.5, came down to line 2390 of
>>> sqlalchemy.orm.query.Query._compile_context.
>>>
>>> Is this a bug or is there a better way to do the above?
>>>
>>> Thanks.
>>>
>>> --
>>> 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
>>> 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 sqlalchemy@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 sqlalchemy@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.
>
2188.patch
Description: Binary data
-- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
