1

I am computing some statistics based on two different filters, but I'm not sure how I can combine both results into a single QuerySet.

My code looks like this:

qs1 = User.objects.filter(c1).annotate(count1=Count("id"))
qs2 = User.objects.filter(c2).annotate(count2=Count("id"))

I would like to create a third queryset, qs3 which has both count1 and count2. Basically I want to left join on User.id but I can't find a straightforward way of doing that.

I also tried

count1 = Count('id', filter=c1)
count2 = Count('id', filter=c2)
qs3 = User.objects.annotate(count1=count1).annotate(count2=count2)

But it gave me different results.

Cory Nezin
  • 1,551
  • 10
  • 22
  • What results do you get from `qs1` and `qs2`? Wouldn't it return just 0 or 1 on each count? – Brian Destura Sep 28 '21 at 00:50
  • No, because as the post in my answer explains `filter` in django is a bit weird. If you filter on related elements, it does a join with those tables, which means the id of a user will appear multiple times, once for each related record. In my case this is exactly what I want. – Cory Nezin Sep 29 '21 at 14:14

1 Answers1

1

I ended up solving with SubQuerys, as described in the first answer here.

qs1 = User.objects.filter(c1).annotate(count=Count('id')).filter(pk=OuterRef('pk'))
qs2 = User.objects.filter(c2).annotate(count=Count('id')).filter(pk=OuterRef('pk'))
users = User.objects.annotate(
    count1=Subquery(qs1.values('count'), output_field=IntegerField()),
    count2=Subquery(qs2.values('count'), output_field=IntegerField())
)

I still don't fully understand the solution since this is the first time I've seen a SubQuery, but it seems to work. If anyone want to provide a more detailed answer I would be happy to accept it.

Cory Nezin
  • 1,551
  • 10
  • 22