How to count a single field of a django queryset with multiple group by?

Let's say I have a queryset qs. I'm grouping by the queyset as follow:

(
    qs.annotate(
        catering_price_enabled=F("outlet__library__settings__sligro_catering_price_enabled"),
    )
    .values("assortment_sync_id", "catering_price_enabled")
    .order_by("assortment_sync_id", "catering_price_enabled")
    .distinct("assortment_sync_id", "catering_price_enabled")
)

And I'm getting something like:

<QuerySet [
   {'assortment_sync_id': '01234', 'catering_price_enabled': False}, 
   {'assortment_sync_id': '01234', 'catering_price_enabled': None}, 
   {'assortment_sync_id': '56789', 'catering_price_enabled': None},
]>

What I'm trying to do is to annotate this queryset so I can eventually filter for values > 1. In other words, each assortment_sync_id can have only value of catering_price_enabled.

If I add .annotate(count=Count("assortment_sync_id")) django raises NotImplementedError: annotate() + distinct(fields) is not implemented. I tried this approach because it obviously works with just one field.

How can I get the expected output below?

<QuerySet [
   {'assortment_sync_id': '01234', 'catering_price_enabled': False, 'count': 2}, 
   {'assortment_sync_id': '01234', 'catering_price_enabled': None, 'count': 2}, 
   {'assortment_sync_id': '56789', 'catering_price_enabled': None, 'count': 1},
]>

"What I'm trying to do is to annotate this queryset so I can eventually filter for values > 1."

If you want to get the objects that assortment_sync_id's bigger than one:

qs.annotate(
catering_price_enabled=F("outlet__library__settings__sligro_catering_price_enabled"),)
.values("assortment_sync_id", "catering_price_enabled")
.order_by("assortment_sync_id", "catering_price_enabled")
.distinct("assortment_sync_id", "catering_price_enabled")

qs.filter(assortment_sync_id__gte=1)

You can drop queryset.distinct() since the group by already taken care of that. Then the annotate count should be working as expected.

(
    qs.annotate(
        catering_price_enabled=F("outlet__library__settings__sligro_catering_price_enabled"),
    )
    .values("assortment_sync_id", "catering_price_enabled")
    .order_by("assortment_sync_id", "catering_price_enabled")
    .annotate(count=models.Count("assortment_sync_id"))
)
Back to Top