Django ORM orderby exact / prominent match to be o

问题:

I need to order the results based on the length of match in Django ORM.

I have a Suburb table with location details in name field.

I have a requirement to search the table with given text and order by exact match / most prominent match to be the top

For example:

1) if search string is 'America' then the result should be [America, South America, North America ..] in this case we found a complete match, which has to be the first element.

2) if search is port then the result should be ['Port Melbourne' 'Portsea', East Airport]

in this case we found port to be a complete match before the delimiter.

I'm aware that i can use several queries and join them, like one for exact match and another for partial match and then join them with exclude on partial match Like

search_list=  [x.name for x in Suburb.objects.filter(name=search)] 
# Then
search_list += [x.name for x in Suburb.objects.filter(name__iregex=r"[[:<:]]{0}".format(search)).exclude(name__in=search_list)]

I can go on like this. But wanted to know if we have any better way.

Any clue ?

Thanks in advance

回答1:

based on func

solution for postgres (and should work in mysql, but not testing):

from django.db.models import Func

class Position(Func):
    function = 'POSITION'
    arg_joiner = ' IN '

    def __init__(self, expression, substring):
        super(Position, self).__init__(substring, expression)


Suburb.objects.filter(
    name__icontains=search).annotate(
    pos=Position('name', search)).order_by('pos')

EDIT: according to Tim Graham's fix, recommended in Django docs - Avoiding SQL injection.

回答2:

Django 2.0 implements a function

StrIndex(string, substring)

Returns a positive integer corresponding to the 1-indexed position of the first occurrence of substring inside string, or 0 if substring is not found.

Example:

from django.db.models.functions import StrIndex

qs = (
    Suburb.objects
    .filter(name__contains='America')
    .annotate(search_index=StrIndex('name', Value('America')))
)