Django ORM Optimisation - annotating by count as well as the last entry for the annotate filter

user_10412414 0
Viewed 52

I currently have the following property on a Post model:

@property
def compliments(self):
    compliments_by_kind = list(
        self.compliment_set.values(
            'kind'
        ).annotate(
            amount=Count('kind')
        ).values(
            'kind',
            'amount'
        )
    )

    for compliment_by_kind in compliments_by_kind:
        compliment_by_kind['last_giver'] = self.compliment_set.filter(
            kind=compliment_by_kind['kind']
        ).order_by(
            'created'
        ).last().giver.name

    return compliments_by_kind

This returns the following listing data structure:

[
  {
    'kind': 'unique', 
    'amount': 3, 
    'last_giver': 'Person 1'
  }, 
  {
    'kind': 'fresh', 
    'amount': 2, 
    'last_giver': 'Person 2'
  }, 
  {
    'kind': 'concept', 
    'amount': 3, 
    'last_giver': 'Person 3'
  }, 
  {
    'kind': 'lines', 
    'amount': 1, 
    'last_giver': 'Person 4'
  }
]

There's nothing wrong with the data, per se. There's just nothing right with ther performance of performing Queries within the loop.

However, the looping method - is not efficient - for each kind (which there are 6 in total, there are 6 further queries on top of the one to get the Count annotation. So, this really hampers performance on the serialization stage.

Would anyone know how to perform the annotation for the latest Compliment "giver.name" according to the ordering by "created" attribute according the the "kind" ... i.e., the last person to give a compliment of "kind" unique etc etc

Here is the Compliment model:

class Compliment(TimeStampedModel):
    giver = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        related_name="giver",
    )
    receiver = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        related_name="receiver",
    )
    post = models.ForeignKey('blog.post', on_delete=models.CASCADE)

    kind = models.CharField(choices=COMPLIMENTS_CHOICES, max_length=15)

UPDATE: Utilising Willem's brilliant suggestion, the solution for me was:

@property
def compliments(self):
    from django.contrib.auth import get_user_model
    from django.db.models import OuterRef, Subquery

    compliments_by_kind = list(
        self.compliment_set.values(
            'kind'
        ).annotate(
            amount=Count('kind'),
            first_name=Subquery(
                get_user_model().objects.filter(
                    giver__artwork_id=self.pk,
                    giver__kind=OuterRef('kind')
                ).values('first_name').order_by('-giver__created')[:1]
            ),
            last_name=Subquery(
                get_user_model().objects.filter(
                    giver__artwork_id=self.pk,
                    giver__kind=OuterRef('kind')
                ).values('last_name').order_by('-giver__created')[:1]
            ),
        )
    )

    return compliments_by_kind
django-orm django python
1 Answers

You can obtain the last_giver in the same query (and thus avoid the N+1 problem), by using a Subquery:

from django.contrib.auth import get_user_model
from django.db.models import OuterRef, Subquery

compliments_by_kind = list(
    self.compliment_set.values(
        'kind'
    ).annotate(
        amount=Count('kind')
        last_name=Subquery(
            get_user_model().objects.filter(
                giver__post_id=self.pk,
                giver__kind=OutRef('kind')
            ).values('name').order_by('giver__created')[:1]
        )
    )
)

That being said, as discussed in the comments, the related_name=… [Django-doc] is the name of the relation in "reverse", so the one to obtain the compliments a user has given or received. Therefore it might be better to rename these to:

class Compliment(TimeStampedModel):
    giver = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        related_name='given_compliments',
    )
    receiver = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        related_name='received_compliments',
    )
    post = models.ForeignKey('blog.post', on_delete=models.CASCADE)
    kind = models.CharField(choices=COMPLIMENTS_CHOICES, max_length=15)

Then the query looks like:

from django.contrib.auth import get_user_model
from django.db.models import OuterRef, Subquery

compliments_by_kind = list(
    self.compliment_set.values(
        'kind'
    ).annotate(
        amount=Count('kind')
        last_name=Subquery(
            get_user_model().objects.filter(
                given_compliments__post_id=self.pk,
                given_compliments__kind=OutRef('kind')
            ).values('name').order_by('giver__created')[:1]
        )
    )
)
user_675790
Related