How to sort a result of objects shown after a search with keyword in Django

Question

Actually i have a working code but the issue that i am facing is how to sort the result of the queryset based on multiple rule. This is my models.py :

class Node(MPTTModel):
    parent              = TreeForeignKey('self', on_delete=models.CASCADE, blank=True, null=True, related_name='children')
    name                = models.TextField(blank=True, null=True)`
    viewed_by           = models.ManyToManyField(CustomUser, related_name='viewed_by',  blank=True)
    bookmarked_by       = models.ManyToManyField(CustomUser, related_name='bookmarked_by', blank=True)
    thumbs_up           = models.ManyToManyField(CustomUser, related_name='thumbs_up', blank=True)

In my views.py i have managed to queryset the database and show all the results based on all matching words, but the missing point here is that i have managed only to sort the result by the number of bookmarks. For i.e : i have this two objects :

Object 1 : name = How to use django forms ?
Object 2 : name = Using django forms for searching the database.

With object 1 is bookmarked by 20 users and Object 2 is bookmarked by 10 users and i type in my search bar : Using django forms database In the result i have the first object as the first answer shown in the list even if the second one have much more matchs with the searched keywords. So what i want to do here is to sort the result first based on the number of matching keywords and than sort it by number of bookmarks. This my view so far :

search_text_imported = request.session['import_search_text']
if search_text_imported != '':
    result_list = []
    get_result_list = [x for x in search_text_imported.split() if len(x) > 2]
    for keyword in get_result_list:
        tree_list = Node.objects.filter((Q(name__icontains=keyword) | Q(Tags__icontains=keyword)), tree_type='root', published=True ).annotate(num_bookmarks=Count('bookmarked_by')).order_by('-num_bookmarks')
        result_list += list(tree_list)
        result = list(OrderedDict.fromkeys(result_list))
    context = {
    'tree_result': result,
    }

Please let me know if there is something missing here, any help will be much appreciated.

I'd solve this by using ranking/scoring. For such simple ranking system you can create a in-database function, which will return a score based on provided keywords and bookmarks count. Then you can annotate your queryset with a custom Func expression (https://docs.djangoproject.com/en/1.11/ref/models/expressions/#func-expressions), and finally order the queryset by annotated field. — marcinn, Jul 27 '19 at 21:51
i have checked the F function but i didn't figure out how can i do it in my case, any example please ? — Farhani Walid, Jul 27 '19 at 23:30
I wrote about `Func` class, not `F()` function. You may define any expression as custom function, then use it via `.annotate(rank=MyCustomRankFunc())`. There are many possibilities. Please look at https://stackoverflow.com/a/46689980/512594 for example. — marcinn, Aug 01 '19 at 10:18

Iain Shelvington · Accepted Answer · 2019-07-28T01:02:43.077

The issue you are having is due to the fact you are creating the result list by concatenating query results together, it does not matter that the queries are sorted if you are concatenating them. You can change your code to only perform a single sorted query by first creating your Q filter and then passing it to a single query

filter = Q()
for keyword in keywords:
    filter |= Q(name__icontains=keyword)
    filter |= Q(Tags__icontains=keyword
result = Node.objects.filter(
    filter,
    tree_type='root',
    published=True
).annotate(
    num_bookmarks=Count('bookmarked_by')
).order_by('-num_bookmarks')

To order by the number of keywords that were matched is a difficult problem. A potential solution is to annotate each Node with a 1 or a 0 for each keyword depending on if there was a match or not and then sum them all

from functools import reduce
from operator import add

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

cases = {}
for i, keyword in enumerate(keywords):
    cases[f'keyword_match_{i}'] = Case(
        When(name__icontains=keyword, then=1),
        default=Value(0),
        output_field=models.IntegerField(),
    )

Node.objects.annotate(**cases).annotate(
    matches=reduce(add, (F(name) for name in cases))
).order_by('-matches')

All together

filter = Q()
cases = {}
for i, keyword in enumerate(keywords):
    filter |= Q(name__icontains=keyword)
    filter |= Q(Tags__icontains=keyword
    # Case is basically an "if" statement
    # If the condition matches then we set the annotated value to 1
    cases[f'keyword_match_{i}'] = Case(
        When(name__icontains=keyword, then=1),
        default=Value(0),
        output_field=models.IntegerField(),
    )
result = Node.objects.filter(
    filter,
    tree_type='root',
    published=True
).annotate(
    **cases
).annotate(
    num_bookmarks=Count('bookmarked_by'),
    keywords_matched=reduce(add, (F(name) for name in cases))
).order_by('-keywords_matched', '-num_bookmarks')

hi there, the code works but it does not sort the result based first on the matching numbers of keywords and then the number of bookmarks, it shows first the object with the most bookmarks even though i type the whole name of the object with the less number of bookmarks. — Farhani Walid, Jul 27 '19 at 23:11
Do you want to sort by the number of keywords that have been matched or by the number of times the keywords appear in the fields? — Iain Shelvington, Jul 27 '19 at 23:17
okey my bad, let's just sort it by the number of keywords that have been matched on just one field which is the name for example — Farhani Walid, Jul 27 '19 at 23:19
It's an interesting problem, I'm going to hack something up to test this — Iain Shelvington, Jul 27 '19 at 23:44
@FarhaniWalid I think I may have a solution, update my answer — Iain Shelvington, Jul 28 '19 at 00:46
Hey, this shows a syntax error ``` cases[f'keyword_match_{i}'] = Case( ``` — Farhani Walid, Jul 28 '19 at 06:44
Ah, f-strings were introduced in python 3.6. You can change it to `cases['keyword_match_{}'.format(i)]` for earlier python versions — Iain Shelvington, Jul 28 '19 at 06:45

How to sort a result of objects shown after a search with keyword in Django

1 Answers1