How to make annotation for foreign key in Django?

Question

I need to replace _points property by annotation so that I have the opportunity to sort entries in the Django admin panel by this value.

My model:

class CustomUser(models.Model):
    inviter = models.ForeignKey('self', on_delete=models.SET_NULL, null=True, blank=True)

    @property
    def _points(self):
        res = CustomUser.objects.filter(inviter_id=self.id).count()
        return res

My admin:

class CustomUserAdmin(admin.ModelAdmin):
    list_display = ['created', 'updated']

    def get_queryset(self, request):
        qs = super(CustomUserAdmin, self).get_queryset(request)
        qs = qs.annotate(points=(HOW TO CALCULATE _points HERE?).order_by('points')
        return qs

Willem Van Onsem · Accepted Answer · 2021-08-16T10:57:51.143

The opposite way of the inviter relation is the customuser, so you can implement this as:

from django.db.models import Count

class CustomUserAdmin(admin.ModelAdmin):
    list_display = ['created', 'updated']

    def get_queryset(self, request):
        return super().get_queryset(request).annotate(
            points=Count('customuser')
        ).order_by('-points')

It might however make more sense to rename the related_name=… [Django-doc] of your inviter to invitees:

class CustomUser(models.Model):
    inviter = models.ForeignKey(
        'self',
        related_name='invitees',
        on_delete=models.SET_NULL,
        null=True,
        blank=True
    )

    @property
    def _points(self):
        return self.invitees.count()

then we thus count this as:

class CustomUserAdmin(admin.ModelAdmin):
    list_display = ['created', 'updated']

    def get_queryset(self, request):
        return super().get_queryset(request).annotate(
            points=Count('invitees')
        ).order_by('-points')

How to make annotation for foreign key in Django?

1 Answers1