Need help in finding the count of an unique group of entries in django models

I have 3 models Customer, Services and Uses. The Uses model contains the customer_id and service_id(service) used by the customer.

Now what I want is a list of count on the number of times each different service being used by the customer. For example if services say pool and buffet is being used once and twice i want the answer to be [1,2]. I don't want the count on distinct services used rather i want the count on each particular service.

Here are my models

class Customer(models.Model):
    firstname = models.CharField(max_length=15)
    lastname = models.CharField(max_length=15)
    #other attributes

class Services(models.Model):
    service_name = models.CharField(max_length=15)
    price = models.IntegerField()

class Uses(models.Model):
    customer = models.ForeignKey(Customer,on_delete=CASCADE)
    service_id = models.ForeignKey(Services,on_delete=CASCADE)
    time_used = models.TimeField(default=timezone.localtime())

This is what I have got which returns the distinct services used.

count=Uses.objects.filter(customer_id=customer).values('customer_id','service_id').distinct().count()

Need some help please to modify it.

1 answer

  • answered 2020-11-20 11:12 Willem Van Onsem

    You can .annotate(…) the Services objects:

    from django.db.models import Count
    
    Services.objects.filter(
        uses__customer=customer
    ).annotate(
        times_used=Count('uses')
    )

    The Services that arise from this queryset will have an extra attribute times_used that will count the number of times the customer used that service.


    Note: normally a Django model is given a singular name, so Service instead of Services.