Django annotate Multiple Sum with one Many to Many field

user_8166263 0
Viewed 154

Lets take this models:

class Computer(models.Model):
    name = models.CharField(max_length=100, null=False, blank=False)
    cpu = models.PositiveIntegerField(null=False, blank=False)
    ram = models.PositiveBigIntegerField(null=False, blank=False)
    customer = models.ForeignKey(
    Customer,
    related_name='Computers',
    on_delete=models.SET_NULL,
    null=True,
    blank=True
    )


class Disk(models.Model):
    computer = models.ForeignKey(
        Computer,
        related_name='Disks',
        on_delete=models.CASCADE,
        null=False,
        blank=False
    )
    size = models.PositiveBigIntegerField(null=False, blank=False)


class Customer(models.Model):
    name = models.CharField(max_length=255,
                            blank=True, null=True)
    creation_date = models.DateTimeField(auto_now_add=True)
    enabled = models.BooleanField(
        blank=False, null=False, default=False)

I want list, by customer, the total number of CPU, RAM, and disk size (with the number of computer for each customer).

something like this:

{'customer': ACME_Corp., 'cpu__sum': 72, 'ram__sum': 10737418240, 'computer__sum': 10, 'disks__sum': 10000000000000}

I tried something like that:

Computer.values('customer').annotate(Sum('cpu'), Sum('ram'), Sum('Disks__size'),computer__sum=Count('computer_id'))

The problem is: If a computer has multiple Disk, I am going to add his cpu (RAM and count) as many time as his number of disk

for example if we have 2 computers with 1 cpu each, but one of them has two disks attached, the cpu total will not be 2, but 3...

Any help will be appreciated, thank you !!

postgresql django-models django
1 Answers

We can work with a subquery to determine the total disk size, and thus prevent that Django makes an extra JOIN:

from django.db.models import OuterRef, Subquery, Sum

Customer.objects.annotate(
    total_cpu=Sum('Computers__cpu'),
    total_ram=Sum('Computers__ram'),
    total_disk=Subquery(
        Disk.objects.filter(computer__customer=OuterRef('pk')).values(
            'computer__customer'
        ).values(
            total_disk=Sum('size')
        ).order_by('computer__customer')[:1]
    )
)

This will make a query that looks like:

SELECT customer.*,
       SUM(computer.cpu) AS total_cpu,
       SUM(computer.ram) AS total_ram,
       (
           SELECT SUM(U0.size) AS total_disk
           FROM disk U0
           INNER JOIN computer U1 ON U0.computer_id = U1.id
           WHERE U1.customer_id = customer.id
           GROUP BY U1.customer_id
           ORDER BY U1.customer_id ASC
           LIMIT 1
       ) AS total_disk
FROM customer
LEFT OUTER JOIN computer ON customer.id = computer.customer_id
GROUP BY customer.id
user_675790
Related