python - Sort by aggregated foreign field in Django ModelAdmin changelist

Question

In my current project I am trying to set up a simple testing app in Django. For management I use the generated Django admin, but I struggle to include a sortable computed field with best test result in changelist view of a model.

My models are as follows (simplified):

class Candidate(models.Model):
   name = models.CharField(max_length=255, null=False)
   email = models.EmailField(unique=True, null=False)

class Test(models.Model):
   candidate = models.ForeignKey(Candidate, on_delete=models.CASCADE, null=False)
   result = models.PositiveIntegerField(null=True)

class Question(models.Model):
   text = models.TextField(null=False)
   correct_answer = models.CharField(max_length=1, choices=OPTIONS, null=False)

class Answer(models.Model):
   test = models.ForeignKey(Test, on_delete=models.CASCADE)
   question = models.ForeignKey(Question, on_delete=models.CASCADE, related_name='answers')
   answer = models.CharField(max_length=1, choices=Question.OPTIONS, null=True)

A candidate may have multiple tests and I want to display a field with his best result in the changelist view and be able to sort by it. The result is a percentage of correct answers (Answer.question.correct_answer == Answer.answer) out of all answers with the same test FK.

Discovered I cannot use a custom computed field defined by a function, because Django then cannot sort by it as sorting needs modification of a queryset which translates directly to SQL. So I added the Test.result field with calculated percentages (which denormalized the scheme :-/ ) and try to add annotated field in queryset with SELECT MAX(Test.result) FROM Test WHERE Test.candidate = {candidate} for every candidate, but cannot find a way how to do it.

The problem is, that the query needs reversed foreign key mapping, because of the 1:M mapping of candidate:test and I haven't found a way how to implement it. This is as far as I got:

class CandidateAdmin(admin.ModelAdmin):
   list_display = ['name', 'email','best_result']
   search_fields = ['name', 'email']

   def get_queryset(self, request):
      queryset = super().get_queryset(request)
      queryset = queryset.annotate(
         _best_result = models.Max('tests_result')
      )
      return queryset

   def best_result(self, obj):
      return obj._best_result

But Django doesn't understand my attempt use MAX on reversed foreign key search of tests_result. Could you advise? Or if I missed a way how to add custom sorting, so I don't need to keep the calculated test result in the database while still sorting by it, I'd be grateful for any hint.

question from:https://stackoverflow.com/questions/65647841/sort-by-aggregated-foreign-field-in-django-modeladmin-changelist

Answer 1

In the end I created a database view with the query, added it to my models with managed = False in Meta and used that instead. Works like a charm.