Django: Converse of `__endswith`

django, django-models, python

Solution

Django ORM is not a silver bullet, there is nothing wrong in writing parts of SQL in case handling with plain ORM is difficult or impossible. This is a really good use case of `extra()`:

Entry.objects.extra(where=['"hello" LIKE CONCAT("%%", name)'])

Note that, since we are writing plain SQL here - it would be database backend specific anyway. This particular is mysql specific and based on this topic: MySQL: What is a reverse version of LIKE?. Should work for PostgreSQL too (haven't tested).

Note that you can adapt the query into a reusable custom `Lookup` (introduced in Django 1.7):

imagine you have the following model

class MyModel(models.Model):
    name = models.CharField(max_length=100)

    def __unicode__(self):
        return self.name

define the `Lookup` class with an `as_sql()` method implemented:

class ConverseEndswith(models.Lookup):
    lookup_name = 'ce'

    def as_sql(self, qn, connection):
        lhs, lhs_params = self.process_lhs(qn, connection)
        rhs, rhs_params = self.process_rhs(qn, connection)
        params = lhs_params + rhs_params
        return '%s LIKE CONCAT("%%%%", %s)' % (rhs, lhs), params

models.Field.register_lookup(ConverseEndswith)

then, here is how our custom `__ce` lookup works in `shell`:

>>> import django
>>> django.setup()
>>> from myapp.models import MyModel
>>> for name in ['hello', 'ello', 'llo', 'test1', 'test2']:
...     MyModel.objects.create(name=name)

>>> MyModel.objects.filter(name__ce='hello')
[<MyModel: hello>, <MyModel: ello>, <MyModel: llo>]
>>> MyModel.objects.filter(name__ce='hello').query.__str__()
u'SELECT `myapp_mymodel`.`id`, `myapp_mymodel`.`name` FROM `myapp_mymodel` WHERE hello LIKE CONCAT("%", `myapp_mymodel`.`name`)'

Another option is to make the check in Python. Since the `LIKE` query would make a full scan through all of the records inside the `Entry` table, you can get them all and check one by one using Python's `endswith()`:

[entry for entry in Entry.objects.all() if 'hello'.endswith(entry.name)]

Problem

Django allows me to do this: ``` chair = Chair.objects.filter(name__endswith='hello') ``` But I want to do this: ``` chair = Chair.objects.filter(name__isendof='hello') ``` I know that the lookup `__isendof` doesn't exist. But I want something like this. I want it to be the converse of `__endswith`. It should find all chairs such that `'hello'.endswith(chair.name)`. Possible in Django? ORM operations are preferable to SQL ones.

Original source

Related problems