Django annotate queryset with counting its members by condition

Question

I have a model:

class Event(models.Model):
    start_date = DateTimeField()
    end_date = DateTimeField()

I want to count all events which are of the same day and include this count as annotation field daily_events to the each member of queryset. I want to solve this on database side for speed. My code so far:

from django.db.models import F, Q, DateTimeField, Count
from django.db.models.functions import TruncDate

events = Event.objects.all()
events = events.annotate(
    daily_events=Count('pk', filter=Q(start_date__date=TruncDate(F('start_date')))))
for event in events:
    print(event.daily_events)

For example I have 4 events, and first 3 at the same day, so desired output should be:

3
3
3
1

but current output is wrong:

1
1
1
1

Any advices?

Answer 1

Unfortunately, with your approach, it is only possible to produce aggregated .values(), kind of {start_date: '2020-10-10', daily_events: 3}. If you really need annotated model objects, it is going to be more complex. One can use a correlated subquery to achieve the desired results.

events = Event.objects.annotate(start_date_date=TruncDate('start_date'))
events_counts = events.values('start_date_date').annotate(daily_events=Count('*'))

subquery = events_counts.filter(start_date_date=OuterRef('start_date_date'))
events = events.annotate(daily_events=Subquery(subquery.values('daily_events'))

Edit: Added the TruncDate part