How to get counts based on multiple conditions with grouping in a single query in django orm?

Question

Suppose that I have the following customer model:

class Customer(models.Model):
    name = models.CharField()
    city = models.CharField()
    first_joined = models.DateField(auto_now_add=True)
    last_visited = models.DateField()
    def __str__(self):
        return self.name

The last_visited field is updated by the code whenever a customer visits the store.

Now, I want to see that in a particular time period, how many new customers joined the store, and how many total customers visited the store. I need this information to be grouped by city.

The output is expected to be something like:

city   |   new    |    visitors
-------------------------------
   A   |    12    |     24
   B   |    34    |     43
   C   |    9     |     21

Currently I am doing it in two seperate queries like this:

visited = Customer.objects.filter(last_visited__gte=from_date, last_visited__lte=to_date).values("city").order_by("city").annotate(count=Count("id"))

new_customers = Customer.objects.filter(first_joined__gte=from_date, first_joined__lte=to_date).values("city").order_by("city").annotate(count=Count("id"))

But I don't prefer this approach for two reasons:

1. It makes the database go through the same set of customers twice while this could be done in one traversal only.
2. It leads to two different dictionaries which is then required to be combined into just one dictionary, which again is an inefficient process.

I have gone through this question, but it only concerns multiple conditions but not how to group them.

Is there a way to do this in Django in a single query?

Answer 1

You can achieve this in a single query by using Django's Case, When, and Sum annotations along with Q objects. These annotations allow you to perform conditional aggregation, enabling you to calculate multiple aggregations in a single query.

You could do something like:

from django.db.models import Count, Case, When, IntegerField, Q

from_date = '2023-01-01'
to_date = '2023-01-31'

result = Customer.objects.filter(
    Q(first_joined__gte=from_date, first_joined__lte=to_date) | 
    Q(last_visited__gte=from_date, last_visited__lte=to_date)
).values("city").annotate(
    new=Count(Case(
        When(first_joined__gte=from_date, first_joined__lte=to_date, then=1),
        output_field=IntegerField()
    )),
    visitors=Count(Case(
        When(last_visited__gte=from_date, last_visited__lte=to_date, then=1),
        output_field=IntegerField()
    ))
).order_by("city")

for r in result:
    print(r)