Re: Support for function application in ORDER BY

Sorry to dig up an old thread, but I've just created a ticket to bring this functionality into django: https://code.djangoproject.com/ticket/24060 Tim Martin, are you interested in bringing this up to date? The alpha freeze for 1.8 is Jan 12th, and I'd really like to get this ready before

Re: Support for function application in ORDER BY

On Monday, 16 June 2014 01:25:45 UTC+1, Josh Smeaton wrote: > > Nice work, I think that's a lot better. The only thing that now worries me > is that order_by doesn't accept sql parameters for some reason. I'm not > sure how many expressions produce sql_params and if they'd be useful in an >

Re: Support for function application in ORDER BY

Nice work, I think that's a lot better. The only thing that now worries me is that order_by doesn't accept sql parameters for some reason. I'm not sure how many expressions produce sql_params and if they'd be useful in an ordering context. It might be worth looking into that a little more. I'm

Re: Support for function application in ORDER BY

On Wednesday, 11 June 2014 12:28:20 UTC+1, Josh Smeaton wrote: > > > Model.objects.filter(...).order_by( F('fld_a').desc(), > F('fld_b')).asc() ) > > Model.objects.filter(...).order_by( (F('fld_a')+F('fld_b')).desc() ) > > I actually really like this. It's simple, clear, and gets around the

Re: Support for function application in ORDER BY

> Model.objects.filter(...).order_by( F('fld_a').desc(), F('fld_b')).asc() ) > Model.objects.filter(...).order_by( (F('fld_a')+F('fld_b')).desc() ) I actually really like this. It's simple, clear, and gets around the issues being discussed. It still requires Expressions to know about

Re: Support for function application in ORDER BY

On Tuesday 10 June 2014 02:48:14 Josh Smeaton wrote: > > However, I think having some special case code in filter(), annotate() > > and anything else that takes expressions would be OK > > I strongly disagree with this. Expressions are context insensitive at the > moment, which means they can be

Re: Support for function application in ORDER BY

> That would be a good way to address it, I think. We are calling prepare() for order_by already. We could add a parameter prepare(..., allow_ordering=False) and throw an exception for non-default ordering unless allow_ordering was set to True. I'd prefer not to include an extra argument to

Re: Support for function application in ORDER BY

> Anything utility functions included in core need a way for 3rd party backends to flag as not supported or be able to tweak the underlying SQL. The most likely tweak would be renaming the function to match the specifics of the database. I have to do this for some of the aggregation functions,

Re: Support for function application in ORDER BY

On Tuesday, 10 June 2014 00:48:14 UTC+1, Josh Smeaton wrote: > > > However, I think having some special case code in filter(), annotate() > and anything else that takes expressions would be OK > > I strongly disagree with this. Expressions are context insensitive at the > moment, which means

Re: Support for function application in ORDER BY

On Mon, Jun 9, 2014 at 7:48 PM, Josh Smeaton wrote: > - should expressions in order_by support random ordering (?) ? I don't > think so, but I haven't ever had a need for random ordering so I'm not sure. > It should be possible to create an expression for order_by that

Re: Support for function application in ORDER BY

I don't have strong opinions on either of the contentious issues in this discussion, but I do want to weigh-in and say that I think this is an important and exciting feature. On Mon, Jun 9, 2014 at 7:48 PM, Josh Smeaton wrote: > > However, I think having some special

Re: Support for function application in ORDER BY

> However, I think having some special case code in filter(), annotate() and anything else that takes expressions would be OK I strongly disagree with this. Expressions are context insensitive at the moment, which means they can be used anywhere. If we start externalising behaviour based on

Re: Support for function application in ORDER BY

On Monday, 9 June 2014 21:28:51 UTC+1, Tim Martin wrote: > > > On Monday, 9 June 2014 20:44:08 UTC+1, Tim Martin wrote: >> >> >> I'll go ahead and try to implement this using __neg__() to invert the >> ordering. >> >> > It's still pretty rough, but there's some code in >

Re: Support for function application in ORDER BY

On Monday, 9 June 2014 20:44:08 UTC+1, Tim Martin wrote: > > > I'll go ahead and try to implement this using __neg__() to invert the > ordering. > > It's still pretty rough, but there's some code in https://github.com/timmartin/django/tree/order_by_expressions that appears to work. In

Re: Support for function application in ORDER BY

oh, nevermind, I misread the syntax spec for the order by clause On Mon, Jun 9, 2014 at 5:07 PM, Michael Manfre wrote: > Each expression must be able to define it's own ordering because order_by > allows you to define a sort based upon multiple expressions. > > > On Mon, Jun

Re: Support for function application in ORDER BY

Each expression must be able to define it's own ordering because order_by allows you to define a sort based upon multiple expressions. On Mon, Jun 9, 2014 at 4:00 PM, Daniel Moisset wrote: > I see everyone trying to put the direction (ascending vs decending) inside >

Re: Support for function application in ORDER BY

I see everyone trying to put the direction (ascending vs decending) inside the expression, while it's actually a property of the ordering clause. Wouldn't it make more sense and avoid ambiguities to do: qs.order_by(..., descending=True) ? Then you can add functions (and even a __neg__ operator

Re: Support for function application in ORDER BY

My concern about the filter(foobar_lt=-F('baz')) issue is that it violates the principle of least surprise: it will be accepted silently and do something that nobody could expect unless they know a bit about the internals. However, I think having some special case code in filter(), annotate()

Re: Support for function application in ORDER BY

The parens definitely would work for __neg__, and as for the output_type problem, it's only an issue when mixing field types - but that can be solved by applying a matching output_type to one of the inner expressions. If a wrapping type isn't needed for neg, I won't introduce one to slightly

Re: Support for function application in ORDER BY

Could we use just F() for this, that is the examples would be: -F(F('field')+F('other')) F(F('field')+5, output_type=IntegerField()) As Marc said in another post plain parentheses should be enough for the first case. - Anssi On 06/09/2014 01:19 PM, Josh Smeaton wrote: If __neg__ is the

Re: Support for function application in ORDER BY

For this example: Model.objects.all().order_by( -Wrap(F('field')+F('other')) ) does just the brackets not work? -(f + g) should do f + g (returning some object) and then negate the result. This doesn't solve your output type issue. If it doesn't python's wrong ;) Marc On 9 June 2014 11:19,

Re: Support for function application in ORDER BY

If __neg__ is the preferred method of applying ordering then I think we should prevent __neg__ from being used in the mathematical sense in the other methods (filter, annotate etc). There hasn't been a need for negating an expression/F() object before, and negation would only work with certain

Re: Support for function application in ORDER BY

Using __neg__ for ordering seems like a good approach to me. I don't like F('-field') that much, if -F('field') works, then F('-field') is duplicate API for the exact same thing. The only problem with -F('field') is that there might be situations where ORDER BY -field ASC gives different

Re: Support for function application in ORDER BY

Agreed. It's worth noting that we could have a `Desc` operator for awkward edge cases where __neg__ doesn't work well, and the default implementation of __neg__ just returns Desc(self). I've not completely considered all the possible implications here, just throwing the idea around. On 9 June

Re: Support for function application in ORDER BY

> That sounds like a workable approach. If I'm understanding right, this means putting the ordering flag on the ExpressionNode class so that all nodes will have this whether or not they are to be used in an ordering context? Yes, that had been what I was thinking, based upon the implementation

Re: Support for function application in ORDER BY

In my opinion, Accounts.objects.filter(balance__lt=-F('overdraft_limit')) would not change the ordering like that. I guess that's an API that doesn't work at the moment anyway. An alternative is to use ~ instead of - meaning inverse instead of negative. This might be more appropriate (but then is

Re: Support for function application in ORDER BY

I'm glad it was easy to implement, I was hoping that would be the case. It also means that expressions are general enough that they can be used elsewhere in the code base. Thanks for following this through! > It might be nice (although possibly impractical) to have something like

Re: Support for function application in ORDER BY

It might be nice (although possibly impractical) to have something like order_by(-LowerCase('title')) [using __neg__ on LowerCase]. That might put logic on the "wrong" objects though. In any case, LowerCase('-title') looks weird. Naturally just passing in the string 'title' or '-title' should

Re: Support for function application in ORDER BY

I've reworked my approach to ORDER BY to base it on Josh's patch, and it works very well. It's very straightforward to implement something like this: Article.objects.order_by(LowerCase('title')) --> ORDER BY LOWER("some_articles"."title") This makes use of all the existing expression

Re: Support for function application in ORDER BY

Funnily enough, I'd already seen that patch but I hadn't figured out the full significance of it. I attempted to solve #22288 (https://code.djangoproject.com/ticket/22288), but spotted that your patch would possibly solve the problem entirely, and at the very least breaks my attempt to solve

Re: Support for function application in ORDER BY

I would like to point you towards a patch I'm currently working on which is essentially your option 2: https://github.com/django/django/pull/2496 It doesn't seem very relevant at first, since it only applies to .annotate and .aggregate, but the infrastructure behind it certainly does. In short,

Re: Support for function application in ORDER BY

With Custom transforms[1] for lookups, we ought to be using the same mechanisms in my opinion. They will also be used for functional indexes in contrib.postgres. We would need to add a __lower transform to all relevant fields of course. I am considering refactoring how the "standard" lookups work

Support for function application in ORDER BY

Hi all, I was looking at implementing the behaviour proposed in #13006 (https://code.djangoproject.com/ticket/13006). In short, the idea is to allow decorating fields in order_by() with a LowerCase() modifier to apply LOWER() in SQL before ordering: