Add "Length" database function

[Danilo Bargen]

Maybe I'm blind, but I can't find a way to use aggregation functions on the length of a database field.

I want to achieve the following:

SELECT MAX(LENGTH(name)) AS max_name_length FROM table;

The natural way to do this with the Django ORM would be something like this:

Model.objects.annotate(name_length=Length('name')).aggregate(Max('name_length'))

Unfortunately there is no such Length() function. A workaround of doing the annotation would be using extra(select={'name_length': 'LENGTH(name)'}), but then the aggregation doesn't work anymore.

My suggestion would be to add such a Length() function.

[Anssi Kääriäinen]

The best you can currently do is creating a custom aggregate, MaxLength. I think it should be somewhat straightforward to do so, you can use django/db/models/sql/aggregates.py and django/db/models/aggregates.py as guidance. The key is to make it like Max, except the template should be MAX(LENGTH()) instead of just MAX().

I do not support adding a Length function to Django. However it would be a good idea to allow users to define their own SQL snippets. Something like:

class Length(SQLExpression):
    def __init__(self, field):
        self.subexpressions = [field]

    def as_sql(self):
        return "LENGTH(%s)" % self.subexpressions[0].as_sql()

and you could use the self-defined Length like you did in your .annotate() example.

I am closing this wontfix as I don't see adding different SQL functions into Django core as the right way forward.

[Aymeric Augustin]

Also, from memory, Length isn't in SQL-92 (I didn't check, feel free to prove me wrong).