How to sort by the sum of a related field in Django using class based views?

Question

If I have a model of an Agent that looks like this:

class Agent(models.Model):
    name = models.CharField(max_length=100)

and a related model that looks like this:

class Deal(models.Model):
    agent = models.ForeignKey(Agent, on_delete=models.CASCADE)
    price = models.IntegerField()

and a view that looked like this:

from django.views.generic import ListView

class AgentListView(ListView):    
    model = Agent

I know that I can adjust the sort order of the agents in the queryset and I even know how to sort the agents by the number of deals they have like so:

queryset = Agent.objects.all().annotate(uc_count=Count('deal')).order_by('-uc_count')

However, I cannot figure out how to sort the deals by the sum of the price of the deals for each agent.

Greg Kaleka · Answer 1 · 2020-01-21T20:40:54.167

2

Given you already know how to annotate and sort by those annotations, you're 90% of the way there. You just need to use the Sum aggregate and follow the relationship backwards.

The Django docs give this example:

Author.objects.annotate(total_pages=Sum('book__pages'))

You should be able to do something similar:

queryset = Agent.objects.all().annotate(deal_total=Sum('deal__price')).order_by('-deal_total')

My spidy sense is telling me you may need to add a distinct=True to the Sum aggregation, but I'm not sure without testing.

edited Jan 21 '20 at 20:40

answered Jan 21 '20 at 20:30

Greg Kaleka

1,942
1
15
32

Adding distinc by field else theт ID may no work with some DB's (hello to MySQL) – Charnel Jan 21 '20 at 20:42
This is almost perfect! I'm getting one minor issue, for some reason, the agent objects with a sum of 0 are getting placed higher than the ones with a sum > 0. After that, however, the order of the sum is perfect. Any idea why this might be happening? – John R Perry Jan 22 '20 at 16:44
Hm, odd. It looks like [this questioner](https://stackoverflow.com/questions/53602865/sort-a-python-django-query-with-zero-always-first-then-positives-then-negative) had the same issue. – Greg Kaleka Jan 22 '20 at 19:01

score 0 · Accepted Answer · answered Jan 27 '20 at 10:58

Building off of the answer that Greg Kaleka and the question you asked under his response, this is likely the solution you are looking for:

from django.db.models import Case, IntegerField, When

queryset = Agent.objects.all().annotate(
    deal_total=Sum('deal__price'),
    o=Case(
        When(deal_total__isnull=True, then=0), 
        default=1, 
        output_field=IntegerField()
    )
).order_by('-o', '-deal_total')

Explanation:

What's happening is that the deal_total field is adding up the price of the deals object but if the Agent has no deals to begin with, the sum of the prices is None. The When object is able to assign a value of 0 to the deal_totals that would have otherwise been given the value of None

How to sort by the sum of a related field in Django using class based views?

2 Answers2

Explanation: