Select longest matching substring using django ORM

user_554807 0
Viewed 109

I have a question which is basically the same as this , except that I want to implement it using the Django ORM if possible.

The linked question says:

How would you search for the longest match within a varchar variable? For example, the_table has entries as follows:

magic_word |  prize
===================
         sh|  $0.20
        sha|  $0.40
       shaz|  $0.60
      shaza|  $1.50

I would like to write a plpgsql function that takes amongst other arguments a string as input (e.g. shazam), and returns the 'prize' column on the row of the_table with the longest matching substring. In the example shown, that would be $1.50 on the row with magic_word shaza.

The answer I want to emulate in Django is

SELECT magic_word
FROM   the_table
WHERE  'shazam' LIKE (magic_word || '%')
ORDER  BY magic_word DESC
LIMIT  1;

... but I can't see how to do the "backwards" LIKE statement.

("Backwards" in the sense that it's the input variable that on the lhs of the LIKE)

django-orm
1 Answers

You could implement a custom lookup, for example:

from django.db import models
from django.db.models import Field
from django.db.models import Lookup

@Field.register_lookup
class PrefixOfLookup(Lookup):
    lookup_name = 'prefix_of'

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

class Xyz(models.Model):
    @staticmethod
    def get_by_longest_match(keyword):
        qs = Xyz.objects.filter(magic_word__prefix_of=keyword).order_by('-magic_word')
        if not qs:
            return None
        else:
            return qs[0]

    magic_word = models.CharField(max_length=200)
    price = models.CharField(max_length=200)

example of usage:

# python manage.py shell
>>> from testapp.models import Xyz
>>> Xyz.objects.filter(magic_word__prefix_of='shazam').order_by('-magic_word')[0].magic_word
'shaza'
>>> Xyz.get_by_longest_match('shazam').magic_word
'shaza'
user_51898990
Related