Reputation: 550
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
Upvotes: 1
Views: 1969
Reputation: 15548
Django 2.0 implements a function
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')))
)
Upvotes: 4
Reputation:
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.
Upvotes: 2