Annotate count of field value occurances in another model

Question

Using Django 1.11. These are my models:

class UserPromoCode(models.Model):
    promo_code = models.ForeignKey(PromoCode, related_name="user_promo_code")

class PromoCode(models.Model):
    code = models.CharField(max_length=20)

I need a queryset that in one or two DB requests does this:

promocodes = PromoCode.objects.all()
for p in promocodes:
   p.assigned_times = UserPromoCode.objects.filter(promo_code__code=p.code).count()

The thing is that PromoCode.code is not unique, so I can't do PromoCode.objects.annontate(assigned_times=Count('user_promo_code')) which is analog of:

promocodes = PromoCode.objects.all()
for p in promocodes:
   p.assigned_times = p.user_promo_code.count()

I guess it should be something like PromoCode.objects.annontate(assigned_times=Count(???)).

See the answer here

andorov
– andorov

2018-09-23 14:32:51 +00:00
Commented Sep 23, 2018 at 14:32 — andorov
– andorov, Commented Sep 23, 2018 at 14:32

Dan Zaikin · Accepted Answer · 2017-05-16 11:14:09Z

7

Ok I got it working.

from django.db.models import OuterRef, Subquery
from django.db.models.functions import Coalesce

user_promo_codes = UserPromoCode.objects.filter(promo_code__code=OuterRef('code')).values('promo_code__code')
user_promocode_counts = upks.annotate(c=Count('*')).values('c')

PromoCode.objects.annotate(assigned_times=Coalesce(Subquery(user_promocode_counts), 0)

Coalesce is used because user_promocode_counts got Nones instead of zeroes.

answered May 16, 2017 at 11:14

Dan Zaikin

3474 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Annotate count of field value occurances in another model

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related