Opened 3 years ago

Closed 3 years ago

Last modified 2 years ago

#18221 closed New feature (wontfix)

Add "Length" database function

Reported by: dbrgn Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: yes UI/UX: no

Description

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.

Change History (3)

comment:1 Changed 3 years ago by akaariai

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to wontfix
  • Status changed from new to closed

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.

comment:2 Changed 3 years ago by aaugustin

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

comment:3 Changed 2 years ago by akaariai

  • Component changed from ORM aggregation to Database layer (models, ORM)
Note: See TracTickets for help on using tickets.
Back to Top