djangodjango-modelsdjango-annotategeneric-foreign-keydjango-polymorphic

How can I annotate django-polymorphic models that have GenericRelations to other models with GenericForeignKeys?

I have a parent model named Content that inherits from Django polymorphic. This is a simplified example, but I have a Post model that inherits from Content.

On the Content model, notice that I have a GenericRelation(Note) named notes.

What I'm trying to do is annotate all Content objects with a count of the number of notes. It's the exact same result you would get in the below for loop.

for content in Content.objects.all():
    print(content.notes.count())

Below is a fully reproducible and simplified example.

To recreate the problem

  1. Setup new Django project, create superuser, add django-polymorphic to the project, and copy/paste the models. Make migrations and migrate. My app was called myapp.
  2. Open manage.py shell, import Post model, and run Post.make_entries(n=30)
  3. Run Post.notes_count_answer() and it will return a list of numbers. These numbers are what the annotated Content PolymorphicQuerySet should show. Example:
Post.notes_count_answer()
[3, 2, 3, 1, 3, 1, 3, 1, 2, 1, 2, 2, 3, 3, 3, 1, 3, 3, 2, 3, 2, 3, 2, 1, 2, 1, 1, 1, 1, 2]

The first number 3 in the list means the first Post has 3 notes.

What have I tried (simplest to complex)

basic

>>> Content.objects.all().annotate(notes_count=Count('notes')).values('notes_count')
<PolymorphicQuerySet [{'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, 
{'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, 
{'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, 
{'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, 
{'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, 
{'notes_count': 0}, '...(remaining elements truncated)...']>

hail-mary / weak attempt

Content.objects.all().prefetch_related('notes').annotate(notes_count=Count('notes')).values('notes_count')
<PolymorphicQuerySet [{'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, 
{'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, 
{'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, 
{'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, 
{'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, {'notes_count': 0}, 
{'notes_count': 0}, '...(remaining elements truncated)...']>

subquery?

>>> Content.objects.all().annotate(notes_count=Subquery(
Note.objects.filter(object_id=OuterRef('pk'), content_type_id=OuterRef('polymorphic_ctype_id')).order_by(
    'object_id').annotate(c=Count('object_id')).values('c'))).values('notes_count')

<PolymorphicQuerySet [{'notes_count': 1}, {'notes_count': 1}, {'notes_count': 1}, 
{'notes_count': 1}, {'notes_count': 1}, {'notes_count': 1}, {'notes_count': 1}, 
{'notes_count': 1}, {'notes_count': 1}, {'notes_count': 1}, {'notes_count': 1}, 
{'notes_count': 1}, {'notes_count': 1}, {'notes_count': 1}, {'notes_count': 1}, 
{'notes_count': 1}, {'notes_count': 1}, {'notes_count': 1}, {'notes_count': 1}, 
{'notes_count': 1}, '...(remaining elements truncated)...']>

close ?

Content.objects.all().annotate(
    notes_count=Count(Subquery(
        Note.objects.filter(
            object_id=OuterRef('pk'), content_type_id=OuterRef('polymorphic_ctype_id')
        ).order_by('object_id')))).values('notes_count')

# error message
line 357, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: sub-select returns 4 columns - expected 1

I've really been trying many different variations of Subquery, but haven't been able to get the correct notes count in the annotation.

Expected result:

Yours wouldn't be exact but the data is generated but this is the idea.

<PolymorphicQuerySet [{'notes_count': 3}, {'notes_count': 2}, {'notes_count': 3}, 
{'notes_count': 1}, {'notes_count': 3}, {'notes_count': 1}, {'notes_count': 3}, 
{'notes_count': 1}, {'notes_count': 2}, {'notes_count': 1}, {'notes_count': 2}, 
{'notes_count': 2}, {'notes_count': 3}, {'notes_count': 3}, {'notes_count': 3}, 
{'notes_count': 1}, {'notes_count': 3}, {'notes_count': 3}, {'notes_count': 2}, 
{'notes_count': 3}, {'notes_count': 2}, {'notes_count': 3}, {'notes_count': 2}, 
{'notes_count': 1}, '...(remaining elements truncated)...']>

requirements.txt

Django==4.1.5
django-polymorphic==3.1.0

settings.py

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'polymorphic',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',

    'myapp.apps.MyappConfig',
]

models.py

from django.contrib.contenttypes.fields import GenericRelation, GenericForeignKey
from django.contrib.contenttypes.models import ContentType
from django.core.validators import MaxValueValidator, MinValueValidator
from django.db import models
from django.conf import settings
from polymorphic.models import PolymorphicModel

from django.contrib.auth import get_user_model


class Vote(models.Model):

    value = models.IntegerField(default=0, validators=[MinValueValidator(-1), MaxValueValidator(1)])

    content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE)
    object_id = models.PositiveIntegerField()
    content_object = GenericForeignKey('content_type', 'object_id')

    def __str__(self):
        return str(self.value)


class Note(models.Model):
    body = models.TextField()

    content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE)
    object_id = models.PositiveIntegerField()
    content_object = GenericForeignKey('content_type', 'object_id')

    def __str__(self):
        return str(self.id)


class Content(PolymorphicModel):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    title = models.CharField(max_length=100)
    votes = GenericRelation(Vote)  # reverse generic relation
    notes = GenericRelation(Note)  # reverse generic relation

    def __str__(self):
        return str(self.pk)


class Post(Content):
    content = models.TextField(blank=True)

    def __str__(self):
        return str(self.pk)

    @staticmethod
    def make_entries(n=5):
        import random
        user = get_user_model().objects.first()
        for i in range(1, n+1, 1):
            vote_count = random.randrange(0, 5)
            note_count = random.randrange(0,3)
            p = Post.objects.create(
                user=user,
                title=f'Post #{i}',
                content=f'Content for post {i}',
            )
            content_type = ContentType.objects.get_for_model(p)
            Vote.objects.create(
                value=vote_count,
                content_type=content_type,
                object_id=p.id
            )
            for j in range(note_count + 1):
                Note.objects.create(
                    body=f'Note {j}',
                    object_id=p.id,
                    content_type=content_type
                )

    @staticmethod
    def notes_count_answer():
        return [content.notes.count() for content in Content.objects.all()]
Solution

  • Did it. I guess the key is knowing that Subquery needs to return one single value (a count) and to perform the count inside the Subquery. I was messing around with Count() function a lot and was banging my head against the wall.

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

Content.objects.annotate(notes_count=Coalesce(
    Subquery(
        Note.objects.filter(
            object_id=OuterRef('pk'),
        ).order_by('object_id').values('object_id').annotate(
            count=Count('object_id')
        ).values('count')
    ), 0
))