Weighted search in Django

Question

I have three inputs coming in from a form. They are name, neighborhoods and tags. Neighborhoods and tags are multi-select box string lists. Here is my current query:

q = Restaurant.objects.filter(name__icontains=name)
q = q.filter(neighborhoods__name__in=neighborhoods)
for tag in tags:
    q = q.filter(tags__name=tag)
q = q.order_by('name').distinct()

Which currently fetches all restaurants that have ALL of the tags and ALL of the neighborhoods. I'm having a little trouble making this a weighted search. Basically, for each tag and neighborhood that matches, I want to add a point to a weight column. Then I will order by weight and even if a restaurant only matches two out of three tags, it will still be shown (its weight would be 2). This is to prevent 0 results from happening and show the closest it can. Additionally, I want to require that at least 1 point is required to select a restaurant.

I guess in SQL it would be something like:

SELECT *, 
    (SELECT COUNT(1) 
     FROM tags t 
     WHERE t.name IN (%s)
    ) AS weight 
FROM restaurants 
WHERE weight > 0 
ORDER BY weight DESC

Answer 1

You want to use annotate()

from django.db.models import Count
q = Restaurant.objects.filter(name__icontains=name)
q = q.filter(neighborhoods__name__in=neighborhoods)
for tag in tags:
    q = q.filter(tags__name=tag)
q = q.order_by('name').annotate(num_tags=Count('tags__name')).filter(num_tags__gte=2)

update

Looking at the code again I see that unfortunately it's filtering out so that only matches with all tags work. I think just this change should work:

Get rid of:

for tag in tags:
    q = q.filter(tags__name=tag)

Replace with:

q = q.filter(tags__name__in=tags)

That way it matches all queries where a Restaurant is tagged with at least one of the requested tags. The annotate and filter later on takes care of making sure it matches at least 2.