Django Aggregate(annotate, count) is working differently.

[Himanshu Pharawal]

models.py

class Intrest(models.Model):
 user = models.ForeignKey(User, related_name='intrests', on_delete=models.CASCADE)
 keyword = models.CharField(max_length=100)

class SponsoredBook(models.Model):
 title = models.CharField(max_length=20)

class Keyword(models.Model):
 sponsored_book = models.ForeignKey(SponsoredBook, related_name='keywords', on_delete=models.CASCADE)
 title = models.CharField(max_length=50, unique=True)

views.py

def get(self, request):
 user = User.objects.filter(id=3).prefetch_related('intrests')
 sub = Subquery(user[0].intrests.annotate(key=Lower('keyword')).values('key'))
 sponsored_books = models.SponsoredBook.objects.annotate(tit=Lower('keywords__title')).filter(tit__in=sub).annotate(points=Count('id')).order_by('points')[:6]
 all_sponsored_books = models.SponsoredBook.objects.all()[:6]
 for sponsored_book in sponsored_books:
 print(sponsored_book.__dict__)
 print()

Case explained

There are two interests stored in db for user(id=3) names as

1. python
2. programming

And three sponsored books stored in db with their correspond interests named as 

1. Test1 - Interests(ptyhon, programming)
2. Test2 - Interests(python, A)
3. Test3 - Interests(B, C)

Problem

For the above database, models and views it shows Test1 book two times and Test2 book one time but expectation was to having Test1 one time and Test2 also one time only the order should be affect but here query set is able to group by books with there id and give them points on the basis of there repetition repetition.

Let me know if you need for more information on the same.