Django: Converse of `__endswith`

Question

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.

Answer 1

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)]    

Answer 2

If you have the possibility to use Django 1.7 you can use custom lookups. Otherwise I think you have to resort to using .extra or .raw.