Django: How to get the most similar record to multi filter query

user_5279166 0
Viewed 364

I have a database of consumer products and I need to filter based on their specs. These queries can be filtering upwards of 10 varying types of fields. This often leads to no exact matches. When there are no exact matches, I would like to return the most similar products. I figured the easiest way to do this would be to annotate a "count" for each column that does match a filter. Then order by products with the greatest "count". Is there a way to do this? Or is there some other way to get similar matches with Django?

For example if I have the following query:

Laptop.objects.filter(brand='Dell', ram=8, price__lte=1000, screen_size=13.3)

If the queryset is empty i'd like to return the laptop with the most matching fields of those 4 filters.

django-queryset django python
1 Answers

An "ugly" solution that I've come across is to chain several calls to annotate() incrementing the same annotation for every match

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

annotated_laptops = Laptop.objects.annotate(
    matches=Value(0)
).annotate(
    matches=Case(When(brand='Dell', then=F('matches') + 1), default=F('matches'), output_field=IntegerField())
).annotate(
    matches=Case(When(ram=8, then=F('matches') + 1), default=F('matches'), output_field=IntegerField())
).annotate(
    matches=Case(When(price__lte=1000, then=F('matches') + 1), default=F('matches'), output_field=IntegerField())
).annotate(
    matches=Case(When(screen_size=13.3, then=F('matches') + 1), default=F('matches'), output_field=IntegerField())
)

Each row in the queryset will then be annotated with the number of matching columns and this can be used to filter or order by

laptops = annotated_laptops.filter(matches=4)
if laptops.count() == 0:
    laptops = annotated_laptops.order_by('-matches')
user_5485620
Related