Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django: Converse of `__endswith`

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.

like image 840
Ram Rachum Avatar asked Jul 13 '14 17:07

Ram Rachum


2 Answers

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)]    
like image 140
alecxe Avatar answered Oct 12 '22 01:10

alecxe


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.

like image 31
rinti Avatar answered Oct 12 '22 01:10

rinti