Django Query to get customer_name who has used a particular keyword maximum no. of times in feedback?

Question

I have these models:

class Customer(models.Model):
    customer_name = models.CharField(max_length=100)

    def __unicode__(self):
        return self.customer_name

class Feedback(models.Model):
    customer_name = models.ForeignKey(Customer)
    feedback_string = models.CharField(max_length=100)

    def __unicode__(self):              
        return self.feedback_string

I want to get the name of customer who has used a particular keyword(for example: "good") maximum times in his/her all the feedbacks. (each feedback contain the particular word only once.)

Answer 1

I think this should work:

Feedback.objects.filter(feedback_string__icontains='good').values('customer_name__ customer_name').annotate(Count("customer_name__ customer_name")).order_by('-customer_name__ customer_name__count')

Here's the idea:

• Find all feedback articles containing 'good'
• Retrieve just the customer_name from that list
• annotate the list by counting just the customer_names
• Order the list by the count then

Answer 2

There are two ways:

1. To get in return a Feedback Dictionary.

   most_occ = Feedback.objects.filter(feedback_string__contains='good')\
       .values('customer_name')\
       .annotate(occ=Count('id'))\
       .order_by('-occ')[:1]
   

2. To get in return a Customer with feedbacks count

   most_occ = Customer.objects.filter(feedback__feedback_string__contains='good')\
       .annotate(num_feedbacks=Count('feedback'))\
       .order_by('-num_feedbacks')[:1]