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

Question

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?

• willeM_ Van Onsem: Can you share the full traceback? I think it is located somewhere else.
• furas: always put full error message because there are other useful information.

Answer 1

But Django raised SyntaxError: positional argument follows keyword argument.

No, Python raises this error. Likely because you have other, non-named parameters, for example:

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')
 ),
 (削除) Count('some_other_count') (削除ここまで),
)

now in Python, you put positional parameters always before named parameters, so f(x, y=z) is valid, (削除) f(y=z, x) (削除ここまで) is not, you thus should inspect the method call that raises the error, and look for such patterns.