Django - SELECT row with largest SUM of related child, GROUP BY related parent

Question

I have three models defined like this:

class Match(models.Model):
    id = models.IntegerField()

class Market(models.Model):
    match = models.ForeignKey(Match,on_delete=models.CASCADE)

class Issue(models.Model):
    market = models.ForeignKey(Market,related_name='issues',on_delete=models.CASCADE)
    volume = models.PositiveIntegerField()

It is structured like this:

Every Match has several Market, and every Market has several Issue.

What I want to do is: For each Match, select the Market that has the largest sum of Issue.volume

It may sound quite easy, but I can't figure it out...

What I've done so far:

1. Without the DISTINCT Match condition

Market.objects.annotate(total_volume=Sum('issues__volume')).order_by("-total_volume")

This works as expected, but a single Match occurs multiple time in my QuerySet.

2. First try with the DISTINCT Match condition

Then I'm trying to add a DISTINCT(Match). As I'm using Postgresql, I can use distinct('match_id'):

Market.objects.annotate(total_volume=Sum('issues__volume')).order_by("match","-total_volume").distinct("match_id")

But it raises the following error: NotImplementedError: annotate() + distinct(fields) is not implemented.

3. Second try with the DISTINCT Match condition

I achieved to get what I expected using:

Market.objects.values('match_id').annotate(total_volume=Sum('issues__volume')).order_by("-total_volume")

However, I want to access to every Issue linked to a specific Market, which is impossible here because of the use of values().

Do you have any ideas on how to make it work with as few requests as possible?

Thank you for your help!

Answer 1

Okay,

It appears to be working by resorting to Subquery:

l = Market.objects.filter(id=OuterRef('pk')).annotate(vol=Sum('issues__volume'))

Market.objects.annotate(total_volume=Subquery(l.values('vol')[:1])).order_by("match_id","-total_volume").distinct("match")

It can prevent the error NotImplementedError: annotate() + distinct(fields) not implemented, but I can't know why exactly!