Order Django QuerySet by where a term appears in results

Question

I'm trying to implement an autocomplete for an input field using Django and jQuery (user searches for a food from a menu). The problem is figuring out how to order the results.

If, for example, the user types "Fish", I want the autocomplete to be ordered like this:

• "Fish and Chips"
• "Fish fry"
• "Fried fish"
• "Fried catfish"

So results would be ordered primarily by where the search term appears in the name, then in alphabetical order after that.

My QuerySet is currently retrieved in Django with

views.py
def load_menu(request):
    term = request.GET['term'] # the search term typed by user
    menu = Menu.objects.filter(item__icontains=term).order_by('item', 'id') # item = name of food
    
    # constructing JSON response
    data = [] 
    for item in menu:
        item = {
            'label': item.item,
            'value': item.id,
        }
        data.append(item)
    return JsonResponse({'data': data})  # received by jQuery function to display autocomplete menu

but that orders results alphabetically only, which I don't want.

How can I order like I specified in the example above?

Answer 1

You achieve this by adding an auxiliary field to the query set. This way you can incorporate your specific logic in that field and use it in the order by clause. Take the below example, order_by_position is added using annotate and case when and assigned 1, 2, 3 based on the following conditions: 1 if the item starts with the term, 2 if the item contains the term, and 3 if the item does not contain the term. Finally, the query is ordered by 'order_by_position', 'item' which makes sure it is first ordered by the specific order in the order_by_position and then alphabetically on item.

from django.db.models import Case, Value, When, CharField

def load_menu(request):
    term = request.GET['term']  # the search term typed by user
    menu = Menu.objects.annotate(
        order_by_position=Case(
            When(item__startswith=term, then=Value(1)),
            When(item__contains=term, then=Value(2)),
            default=Value(3),
            output_field=CharField(),
        )
    ).filter(item__icontains=term).order_by('order_by_position', 'item')

    # constructing JSON response
    data = []
    for item in menu:
        item_data = {
            'label': item.item,
            'value': item.id,
        }
        data.append(item_data)
    return JsonResponse({'data': data})

To go even further and be more specific you can also consider the index of matched term:

    from django.db.models import CharField, Value, Func, PositionField

class StartsWith(Func):
    function = 'POSITION'
    arity = 2
    output_field = PositionField()

def load_menu(request):
    term = request.GET['term']  # the search term typed by user
    menu = Menu.objects.annotate(
        start_pos=StartsWith(F('item'), Value(term)),
    ).filter(item__icontains=term).order_by('start_pos', 'item')

    # constructing JSON response
    data = []
    for item in menu:
        item_data = {
            'label': item.item,
            'value': item.id,
        }
        data.append(item_data)
    return JsonResponse({'data': data})