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 so that text-based lookups are only available on those fields which
are string based, and there is a __text transform on all other fields. This
certainly makes sense in postgres, I've not looked at other dbs.
The syntax would then become .order_by('title__lower'). I have to admit I'm
not familiar with how we add ORDER BY clauses as yet, but ideally we would
be using something similar to the query expression API[2] - i.e. something
with an as_sql and an as_vendorname_sql.
If you've had a look at the code, does this kind of approach seem like it
might work?
As an aside, I'd also like transforms to be available in a couple of other
places - most notably in values() calls and F() objects. So although they
are currently specific to .filter() and .exclude(), the concept does (and
hopefully will in the future) translate.
[1]
https://docs.djangoproject.com/en/dev/ref/models/custom-lookups/#a-simple-transformer-example
[2]
https://docs.djangoproject.com/en/dev/ref/models/custom-lookups/#the-query-expression-api
On 31 May 2014 12:22, Tim Martin <[email protected]> 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/141c1486-ca35-497d-82f7-13ad83900b9d%40googlegroups.com
> <https://groups.google.com/d/msgid/django-developers/141c1486-ca35-497d-82f7-13ad83900b9d%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> For more options, visit https://groups.google.com/d/optout.
>
--
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/CAMwjO1Hg39c9Wwxfbn_69Xhzp2SDO2qKQk3wWxxJ0mA7H54%3D8Q%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
