I have three models which are related all to one model.

class MyModelParent(models.Model):
      name = models.CharField(max_lenght=36, blank=True)

      def __str__(self):
          return self.name or ""

class MyFirstModel(models.Model):
      mymodelparent = models.ForeignKey(MyModelParent, related_name="first_models", blank=True, Null=True, on_delete=models.CASCADE
      ranking = models.IntegerField(max_lenght=36, blank=True)

      def __str__(self):
          return self.name or ""

class MySecondModel(models.Model):
      mymodelparent = models.ForeignKey(MyModelParent, related_name="second_models", blank=True, Null=True, on_delete=models.CASCADE
      ranking = models.IntegerField(max_lenght=36, blank=True)

      def __str__(self):
          return self.name or ""

class MyThirdModel(models.Model):
      mymodelparent = models.ForeignKey(MyModelParent, related_name="third_models", blank=True, Null=True, on_delete=models.CASCADE
      ranking = models.IntegerField(max_lenght=36, blank=True)

      def __str__(self):
          return self.ranking or ""

I am rendering MyParentModel in DetailView (CBV) and passing related models as a context to render individual models 'ranking' field on the same template.

Now I need to render same 'ranking' on MyParentModel ListView, but I only want to display 'ranking' which has highest value. Question is, how I can compare my related models 'ranking integer value' and display highest on MyParentModel ListView page?

I am on learning curve so appreciate your help...

CodePudding user response：

You can compare different fields using Greatest and compare the values of the fields themselves using Max() - an aggregation will return to you the single greatest value:

from django.db.models import Max
from django.db.models.functions import Greatest

MyModelParent.objects.aggregate(
    max=Greatest(
        Max('first_models__ranking'),
        Max('second_models__ranking'),
        Max('third_models__ranking'),
    )
)

# output: {'max': 230}

Edit - Explanation

There are three parts to this query:

1. Max - this finds the maximum value of the provided field, we are using double underscore notation to access the ranking field of the related model because ranking is not a field of MyModelParent.

2. Greatest - because Max only works on fields, we cannot use it to find the max of the max (if that makes sense). Instead, we need to use Greatest which is a DB function (whereas Max lives within django.db.models) and returns the greatest value from said list. We therefore provide it with each of our Max values from the three different models.

3. Aggregate - we just want the single highest figure and aggregate will squash the queryset into a dictionary - we can add other values to this if we want. Annotate can also be used if we want each queryset object to have access to this value.