2

I have two models like this:

class Item(models.Model):
   title = models.CharField(max_length=128)


class Order(models.Model):
   item = models.ForeignKey(Item)
   user = models.ForeignKey('users.User',null=True)
   gift_code = models.CharField(max_length=20,default='')

I need to annotate two different query:

Order.objects.filter(gift_code__isnull=False, gift_code__gt='').values('item__title').annotate(the_count=Count('item__title'))

Order.objects.filter(gift_code__isnull=False, gift_code__gt='', user__isnull=False).values('item__title').annotate(the_count=Count('item__title'))

the problem is I can't find a way to merge this two query.

I tried this method, but both value was the same:

all_gift = Count('item__title')
used_gift = Count('item__title', filter=Q(user__isnull=False))
gifts = Order.objects.filter(gift_code__isnull=False, gift_code__gt='').values('item__title').annotate(all_gift=all_gift).annotate(used_gift=used_gift)

the actual output:

[{'item__title': 'title', 'used_gift': 500, 'all_gift': 500},...]

my expected output is:

[{'item__title': 'title', 'used_gift': 60, 'all_gift': 500},...]
nim4n
  • 1,813
  • 3
  • 21
  • 36

1 Answers1

0

You could add a distinct parameter to your Count():

used_gift = Count('item__title', filter=Q(user__isnull=False), distinct=True)

This way it should eliminate the duplicates caused by the other annotate, and return the expected 60 gifts.

Vitor Freitas
  • 3,550
  • 1
  • 24
  • 35
  • It does not work, it returned 1 for all used_gift value. – nim4n Feb 13 '18 at 06:19
  • I discover that the filter feature was added to django 2 version and my project is with django 1.11 and the filter does not exist in this version. – nim4n Feb 14 '18 at 18:00
  • not sure if something like that would work, but it's worth trying: `Order.objects.filter(gift_code__isnull=False, gift_code__gt='').values('item__title').annotate(the_count=Count('item__title')).filter(user__isnull=False).annotate(the_count=Count('item__title', distinct=True))` but i'm not 100% sure on how this query would be evaluated – Vitor Freitas Feb 14 '18 at 18:08
  • it does not work, it evaluate both filter and then annotate. – nim4n Feb 14 '18 at 19:07