[Python] How to use Django Q objects with ~Q() inside annotate(filter=...) to exclude a value?

I'm refactoring a legacy Django Job to use annotate with filtered Count aggregations instead of querying each record individually (avoiding the N+1 problem).

I want to count the number of related EventReport objects per Store, excluding those where status="C".

So I wrote something like:

stores_with_monitoring_enabled.annotate(
total_cards=Count(
'eventreport',
filter=Q(
eventreport__event_at__gte=day_30_days_ago_start,
eventreport__event_at__lte=yesterday_end
) & ~Q(eventreport__status='C')
),
# ... etc


But Django raised SyntaxError: positional argument follows keyword argument.

I also tried:

# ... etc

filter=Q(
eventreport__event_at__gte=start_date,
eventreport__event_at__lte=end_date
) & ~Q(eventreport__status="C")

# ... etc


But I'm unsure if this is the correct pattern inside annotate()'s filter.

I expected to get only related objects where `status != "C" without any errors.

PS: I looked into other solutions on StackOverflow and the suggestions on this one: How do I do a not equal in Django queryset filtering?, but I could'nt get it working when using Q() alongside ~Q() with other kwargs.

What’s the best approach to express status != 'C' inside a Count(..., filter=...) clause?

Continue reading...