Django annotate multiple aggregators over grouped values

Due to the structure of my project, I need to have multiple aggregations over three interlocked tables. With structure looking somewhat like this:

class ItemMeta(models.Model):
    item = models.ForeignKey(
        Item, on_delete=models.SET_NULL, null=True
    )
    class = models.CharField(max_length=2048, null=True, blank=True)
    department = models.CharField(max_length=2048, null=True, blank=True)

class Item(models.Model):
    amount = models.DecimalField(max_digits=18, decimal_places=2)
    status = models.CharField(max_length=2048, null=True, blank=True, choices=ItemStatus.choices)

class CartItem(models.Model):
    author = models.ForeignKey(to=UserModel, on_delete=model.CASCADE)
    item = models.ForeignKey(Item, on_delete=models.CASCADE)

class ItemStatus(models.TextChoices):
    UNDER_CONSIDERATION = 'UNDER_CONSIDERATION', 'Under consideration'
    IMPOSED = 'IMPOSED', 'Imposed'
    PAID = 'PAID', 'Paid'

And I need to have item grouping by class, department and status both in cart and outside of it. I also need to have aggregations of combined amounts of items in different statuses, as well as counts of different items in cart and existing. So the structure of the response has to always contain 5 values: sum of paid, imposed and considered items, and count of items existing and in cart of the calling user. I inherited from last poor sod this piece of code to do these:

 def _sum(self, status):
    return Coalesce(Sum('amount', filter=Q(status=status)), 0.0, output_field=FloatField())

 def annotate_kwargs(self):
    return {
        'under_consideration_amount': self._sum(ItemStatus.UNDER_CONSIDERATION),
        'imposed_amount': self._sum(ItemStatus.IMPOSED),
        'paid_amount': self._sum(ItemStatus.PAID),
        'count': Count('pk', distinct=True),
        'in_cart': Count('pk', distinct=True, filter=Q(cartitem__author=self.user)),
    }

def get(self):
    return self.queryset \
        .values(*self.group_by) \
        .annotate(**self.annotate_kwargs())

Which basically takes the Item queryset and groupes it according to request and then annotates it. Problem is, it returns lies, as is highlighted in the docs. Methinks having 3 different tables has something to do with it, but at this point i have no way to change the model structure, so it has to stay as it is or have as little change as possible. My question is how to have these aggregations? I tried using subquery, but i don't know how to make it work with .values clause

Back to Top