[Fixed]-Django – Filter queryset by CharField value length

by

28👍

‘Recent’ Django versions have the built in django.db.models.functions.Length lookup/transform, so you can do:

MyModel.objects.annotate(length=Length('text')).filter(length__gt=254)

See https://docs.djangoproject.com/en/1.11/ref/models/database-functions/#length

Old answer:

I think you have to options:

Using ‘extra’ on the queryset:

MyModel.objects.extra(where=["CHAR_LENGTH(text) > 254"])

Or abusing Regex lookups, I’m assuming this will be slower:

MyModel.objects.filter(text__regex = r'^.{254}.*')
👤BBT

7👍

If you find that you are using a lot of extra and regex, following @BBT’s suggestions, I went ahead and implemented the transform as follows:

# utils.db

from django.db.models import Transform
from django.db.models import CharField

class CharacterLength(Transform):
    lookup_name = 'len'
    def as_sql(self, compiler, connection):
        lhs, params = compiler.compile(self.lhs)
        return "LENGTH(%s)" % lhs, params

CharField.register_lookup(CharacterLength)

Thereafter, I could do a cascaded lookup on “mycolname” as follows:

from utils.db import *

queryset.filter(mycolname__len__gte=10)
👤Lord Mosuma

Leave a comment