[Django]-Django filter on the basis of text length

by

-21👍

✅

It would be much better and faster if you just add a column that pre-calculates(memoizes) the length
of the text.

e.g.

class MyModel(models.Model):
    text = models.TextField()
    text_len = models.PositiveIntegerField()

     def save(self, *args, **kwargs):
         self.text_len = len(self.text)
         return super(MyModel, self).save(*args, **kwargs)

MyModel.objects.filter(text_len__gt = 10)     # Here text_len is pre-calculated by us on `save`
👤rantanplan

298👍

For modern Django (>=1.9) @hynecker‘s answer is better.

For Django >= 1.8 you can use an annotation and the Length function:

from django.db.models.functions import Length
qs = MyModel.objects.annotate(text_len=Length('text_field_name')).filter(
    text_len__gt=10)

Under the hood, Django uses is @Pratyush’s suggested CHAR_LENGTH() MariaDB (MySQL) function. But the Django Length function will work for any Django-compatible db by using LENGTH() for Postgres and other databases.

👤hobs

67👍

Another way is:

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

This can be used where the text lenght is more than 255 characters too.

👤Pratyush

64👍

A nice solution for Django >= 1.9 is possible by registering the builtin function Length as a Transform for CharField lookup.

Register the transformation in the project once. (The best place is probably models.py.)

from django.db.models import CharField
from django.db.models.functions import Length

CharField.register_lookup(Length, 'length')

Use:

result = MyModel.objects.filter(text__length__gt=10)

See exactly the same example in docs for Length as a transform.

It works correctly for all backends, compiled by LENGTH() for most backends and by CHAR_LENGTH() for MySQL. It is then registered for all subclasses of CharField automatically, e.g. for EmailField. The TextField must be registered individually. It is safe to register the name “length”, because a transform name could never shade or be shaded by an equally named field name or related field name.

The only disadvantage could be readability puzzle: Where did the “length” come from? (The lookup is global, but the same can be luckily safely registered repeatedly in more modules, if useful for readability, without any possible overhead at query runtime.)

Other similarly valuable solution is the hobs‘s above that is shorter if a registration counts and if a similar query is not used repeatedly.

👤hynekcer

34👍

You can use the regex filter to search for text of a particular length:

MyModel.objects.filter(text__regex = r'.{10}.*')

Caveat: for MySQL, the maximum length value is 255. Otherwise an exception is thrown:

DatabaseError: (1139, "Got error 'invalid repetition count(s)' from regexp")
👤Iain Shelvington

-9👍

I would solve the problem on your app server and not tax your database. You can do this by:

models_less_than_ten = []
mymodel = MyModel.objects.all()
for m in mymodel:
    if len(m.text) > 10:
          models_less_than_ten.append(m)
👤Atma

Leave a comment