Django ORM orderby exact / prominent match to be on top

Question

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

I'm using MySQL. I have got a working solution all by using python. Will try what you are suggesting. I would like to have it in ORM though — Seshadri VS, Sep 26 '17 at 00:55
[safety vertion](https://stackoverflow.com/a/46498139/8060120) — Brown Bear, Sep 29 '17 at 23:38

score 4 · Answer 1 · edited Jun 20 '20 at 09:12

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

score 2 · Accepted Answer · edited Nov 04 '17 at 10:53

2

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.

edited Nov 04 '17 at 10:53

hynekcer

14,942
6
61
99

answered Sep 25 '17 at 07:50

Brown Bear

19,655
10
58
76

Serious security alert: Sanitize the search string. Otherwise this example is open to SQL injection attack. (Verified by removing a table by search string.) – hynekcer Sep 25 '17 at 16:16
@hynekcer could you show example of attack search string, please. – Brown Bear Sep 25 '17 at 20:10
I recommend to use the name and parameters like the future [django.db.models.functions.StrIndex docs](https://docs.djangoproject.com/en/dev/ref/models/database-functions/#strindex) from Django 2.0 alpha 1 and copy & paste that [django.db.models.functions.base.StrIndex](https://github.com/django/django/blob/45cd53eee8ca98d4e22adb727151c32a416fe175/django/db/models/functions/base.py#L167) – hynekcer Sep 26 '17 at 00:47
Do you not want to ask a question how to write more complicated functional expressions? Issues, limitations and solutions for functions with more parameters, nested functions in the body, when can not rely on automatic escaping? I think that the question could be high voted, viewed and commented and finally someone could could write a shorter PR to Django docs. The question above is too broad and not about Func. Other questions are similarly "I am trying to... (something very particular)", not a question for a general answer How to. (SO is perfect thanks to user comments and editing history.) – hynekcer Sep 26 '17 at 11:34
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/155326/discussion-between-hynekcer-and-bear-brown). – hynekcer Sep 26 '17 at 11:39
I've opened a [pull request](https://github.com/django/django/pull/9202) to add a warning to the documentation about this mistake. It would be great to edit the accepted answer based on that so that it isn't vulnerable to SQL injection. – Tim Graham Oct 19 '17 at 18:57
Thanks to Tim Graham (the main Django developer) that he prepared the text about security of Func() in Django docs on your example. I waited for a decision that this security issue will be solved only by documentation. – hynekcer Oct 20 '17 at 23:02

Django ORM orderby exact / prominent match to be on top

2 Answers2

StrIndex(string, substring)

Linked