Simple Subquery with OuterRef

Question

I am trying to make a very simple `Subquery` that uses `OuterRef` (not for practical purposes, but just to get it working), but I keep running into the same error. `posts/models.py` code ``` from django.db import models class Tag(models.Model): name = models.CharField(max_length=120) def __str__(self): return self.name class Post(models.Model): title = models.CharField(max_length=120) tags = models.ManyToManyField(Tag) def __str__(self): return self.title ``` `manage.py shell` code ``` >>> from django.db.models import OuterRef, Subquery >>> from posts.models import Tag, Post >>> tag1 = Tag.objects.create(name='tag1') >>> post1 = Post.objects.create(title='post1') >>> post1.tags.add(tag1) >>> Tag.objects.filter(post=post1.pk) ]> >>> tags_list = Tag.objects.filter(post=OuterRef('pk')) >>> Post.objects.annotate(count=Subquery(tags_list.count())) ``` The last two lines should give me number of tags for each Post object. And here I keep getting the same error: ``` Val…

Accepted Answer

One of the problems with your example is that you cannot use `queryset.count()` as a subquery, because `.count()` tries to evaluate the queryset and return the count.

So one may think that the right approach would be to use `Count()` instead. Maybe something like this:

```
Post.objects.annotate(
    count=Count(Tag.objects.filter(post=OuterRef('pk')))
)
```

This won't work for two reasons:

The `Tag` queryset selects all `Tag` fields, while `Count` can only count on one field. Thus: `Tag.objects.filter(post=OuterRef('pk')).only('pk')` is needed (to select counting on `tag.pk`).

`Count` itself is not a `Subquery` class, `Count` is an `Aggregate`. So the expression generated by `Count` is not recognized as a `Subquery` (`OuterRef` requires subquery), we can fix that by using `Subquery`.

Applying fixes for 1) and 2) would produce:

```
Post.objects.annotate(
    count=Count(Subquery(Tag.objects.filter(post=OuterRef('pk')).only('pk')))
)
```

However if you inspect the query being produced:

```
SELECT 
    "tests_post"."id",
    "tests_post"."title",
    COUNT((SELECT U0."id" 
            FROM "tests_tag" U0 
            INNER JOIN "tests_post_tags" U1 ON (U0."id" = U1."tag_id") 
            WHERE U1."post_id" = ("tests_post"."id"))
    ) AS "count" 
FROM "tests_post" 
GROUP BY 
    "tests_post"."id",
    "tests_post"."title"
```

you will notice a `GROUP BY` clause. This is because `COUNT` is an aggregate function. Right now it does not affect the result, but in some other cases it may. That's why the docs suggest a different approach, where the aggregation is moved into the `subquery` via a specific combination of `values` + `annotate` + `values` :

```
Post.objects.annotate(
    count=Subquery(
        Tag.objects
            .filter(post=OuterRef('pk'))
            # The first .values call defines our GROUP BY clause
            # Its important to have a filtration on every field defined here
            # Otherwise you will have more than one group per row!!!
            # This will lead to subqueries to return more than one row!
            # But they are not allowed to do that!
            # In our example we group only by post
            # and we filter by post via OuterRef
            .values('post')
            # Here we say: count how many rows we have per group 
            .annotate(count=Count('pk'))
            # Here we say: return only the count
            .values('count')
    )
)
```

Finally this will produce:

```
SELECT 
    "tests_post"."id",
    "tests_post"."title",
    (SELECT COUNT(U0."id") AS "count" 
            FROM "tests_tag" U0 
            INNER JOIN "tests_post_tags" U1 ON (U0."id" = U1."tag_id") 
            WHERE U1."post_id" = ("tests_post"."id") 
            GROUP BY U1."post_id"
    ) AS "count" 
FROM "tests_post"
```

Simple Subquery with OuterRef

Solution

Problem