Django Counting related object with a certain condition

Question

I have this model classes in my django app:

class Ad(models.Model):
    ...

class Click:
    time = models.DateTimeField(auto_now_add=True)
    ip = models.GenericIPAddressField()
    ad = models.ForeignKey(
        to=Ad,
        related_name='views',
        on_delete=CASCADE
    )

class View:
    time = models.DateTimeField(auto_now_add=True)
    ip = models.GenericIPAddressField()
    ad = models.ForeignKey(
        to=Ad,
        related_name='views',
        on_delete=CASCADE
    )

Assume I have a queryset of Ad objects. I want to annotate the count of clicks for each add that happened in hour 12 to 13 (We could use range look-up). First I did it like this:

query.filter(clicks__time__hour__range=[12, 13]).annotate(views_count=Count('views',distinct=True), clicks_count=Count('clicks', distinct=True))

but those ads which don't have any clicks in that range will be omitted from the query this way but I need them to be present in the final query.

Is there any proper way to do so maybe with Django Conditional Expressions?

score 0 · Answer 1 · answered Nov 17 '20 at 22:22

As per the docs you should be able to do the filter in the Count aggregate.

from django.db.models import Count, Q
query.annotate(
    views_count=Count('views',distinct=True),
    clicks_count=Count('clicks', distinct=True, filter=Q(time__hour__range=[12, 13])),
)

Django Counting related object with a certain condition

1 Answers1