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, the patch expands on the concept of "Expressions". Expressions are nodes in a tree that output SQL. Functions can be expressions, a field (F() object) can be an expression, or the number 5 can be an expression. Funnily enough, I implemented the SQL `Lower` function as a test: https://github.com/jarshwah/django/commit/362fec1330f1f40c45ece5769f9e35047c87a010#diff-4c9f3e475cf7fa25e6d21c2fb7b8c30fR185 What I'd like to do if this patch is accepted (and I believe it will be for 1.8; it's a matter of getting the backwards-compat right), is to expand the concept of expressions into the other pieces of the API, especially `order_by`. Once order_by understands expressions, it's trivial to order the result set by any kind of sql you could imagine.. - .order_by(NullsLast('field')) - .order_by(Lower('field')) - .order_by(Coalesce(F('field'), F('otherfield'))) etc. Marc is right that custom transforms could be used if order_by was taught the API. I would prefer to teach order_by about Expressions though, since F() supports custom lookups and transforms already. His version would then look like: .order_by(F('title__lower')). I can see the value in a keyword__based API that is similar to the rest of django (like .filter()), but I find the syntax limiting when trying to compose multiple expressions together. If you were interested in branching off and working on bringing the same functionality to order_by, I'd be happy to provide information or support if you had any questions. Regards, Josh On Saturday, 31 May 2014 21:22:14 UTC+10, Tim Martin wrote: > > 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: > > Articles.objects.all().order_by(LowerCase('title')) > > to yield > > ... > ORDER BY LOWER("articles"."title") > ... > > Obviously it's sensible if we can generalise this to apply a wide range of > SQL functions with the same infrastructure. > > It's easy enough to make this work, but I'm having trouble with > generalising the solution. The problem is that the argument to order_by() > can be a compound expression like "-author__name" and we have to parse it > out. Only the SQLCompiler object (I think) has the necessary information to > correctly figure out the joins and turn this into valid SQL. > > I can see a couple of different approaches: > > 1) Have these decorator classes be very simple containers and keep all the > logic in the SQLCompiler. The compiler object will detect a > FunctionCallDecorator, query it for the ordering string and combine them, > something like: > > if isinstance(field, FunctionCallDecorator): > table, cols, order = self.find_ordering_name(field.get_field(), ...) > > function = field.get_function() > > # other stuff.... > order_by = "%s(%s.%s)" % (function, qn(table), qn2(col)) > > I'm not that happy with this solution, since it binds the implementation > of FunctionCallDecorator very closely to the workings of SQLCompiler and to > ORDER BY in particular. > > 2) Make the argument to order_by into a tree structure that can be > processed by the SQLCompiler using the visitor pattern. Leaf nodes > (strings) get mapped into expressions by the SQLCompiler as normal, while > internal nodes get to define their own logic for combining the output of > their child nodes into an expression. The ASC / DESC direction is a bit > awkward here, since it's meaningless to have two function arguments that > order in different directions, for example. > > This is more similar to how we do WHERE nodes, if I'm understanding the > code correctly. This seems to better support multi-argument functions (e.g. > COALESCE) which would be a horrible kludge in the first method, and to > support multiple function application (e.g. LOWER(LTRIM(...))). However, it > feels a bit over-engineered. > > There are obviously lots of other variations here, but I figured I'd ask > before I went too much further with the design process. Does anyone have > any feelings about how best to approach this? > > Tim > -- You received this message because you are subscribed to the Google Groups "Django developers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/7bb3ebba-d2e6-4eed-acf2-20b6ecb1960e%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
