Django: sort rows by number of column matches
I have a model: -
class Userprofile(models.Model):
user=models.OneToOneField(settings.AUTH_USER_MODEL)
education=models.models.CharField(max_length=20,blank=True,null=True)
country=models.CharField(max_length=20,blank=True,null=True)
occupation=models.CharField(max_length=20,blank=True,null=True) ....
for a user profile (say: ('masters', 'India', 'student'). I want to filter all user profiles, ordered by the number of fields that it matches a given user profile, i.e. first all 3 fields are matching profiles, then any 2 fields corresponding to profiles, etc. Can anyone suggest a way to do this efficiently?
+3
source to share
1 answer
You can achieve this using conditional expressions .
from django.db.models import Value, Case, When, IntegerField, F
education, country, occupation = 'masters','India','student'
Userprofile.objects.annotate(education_matched=Case(
When(education=education, then=Value(1)),
default=Value(0),
output_field=IntegerField()
), country_matched=Case(
When(country=country, then=Value(1)),
default=Value(0),
output_field=IntegerField()
), occupation_matched=Case(
When(occupation=occupation, then=Value(1)),
default=Value(0),
output_field=IntegerField()
)).
annotate(matched=F('education_matched') + F('country_matched') + F('occupation_matched')).
order_by('matched')
+1
source to share