Django annotate with Count on subquery with OuterRef failing

Question

TLDR:

Lets hit overall problem here 1st:

How in django annotate queryset with a Subquery that uses OuterRef and count how many objects subquery returned (for every object in original query)

Pseudocode:

Model.objects.annotate(count=Count(Subquery(Model2.objects.filter(rel_id=OuterRef("id")).values_list("some_field").distinct())))

Full description

I have couple of models:

class Model1(models.Model):
    model2 = models.ForeignKey(Model2, related_name="one_model2")
    model2_m2m = models.ManyToMany(Model2, related_name="many_model2")
    new_field = models.BooleanField()
class Model2(models.Model):
    model3 = models.ForeignKey(Model3)
class Model3(models.Model):
    ...

I need to make a query that checks for every Model1 objects whether for every Model2 related objects (through model2 or model2_m2m) their related model3 uniquely count more than 1. If yes, set new_field to True.

For example model1 related with model2.model3 = 1 and model2_m2m = [model2.model3 = 1] This results in new_field = False but model1 related with model2.model3 = 1 and model2_m2m = [model2.model3 = 2] Results in it being True

So what was my approach?

I created suquery from Model2:

sub_model2 = Model2.objects.filter(one_model2=OuterRef("id") | many_model2=OuterRef("id"))

And then used it in various ways to annotate Model1:

Model1.objects.annotate(my_annotation=Subquery(sub_model2.values_list("model3", flat=True).distinct().aggregate(<here is counting>)

Result in error that This should be put in subquery(? isnt it?) [This queryset contains a reference to an outer query and may only be used in subquery]

When trying to Count it as this:

Model1.objects.annotate(my_annotation=Count(Subquery(sub_model2.values_list("model3", flat=True).distinct()) 

It is ok as long as sub_model2 returns only 1 results. With 2 it has a problem that subquery returns more than 1 row (isn't that a purpose of my Count?)

and some more with the same results.

Note, using annotate on subquery will result in always returning 1 as every model2 has relation to model3 (mostly). It would never sum it up!

And no, this Django 1.11 Annotating a Subquery Aggregate is way to easy example/solution

Answer 1

You can calculate count inside subquery like this:

Model.objects.annotate(count=Subquery(Model2.objects.filter(rel_id=OuterRef("id")).annotate(count=Count("some_field", distinct=True)).values_list("count")[:1]))