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

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?

Solution

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.