Django: SUM of COUNT in annotations

Question

I have the following simple models:

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


class Booking(models.Model):
    date = models.DateField(default=now)
    price = models.DecimalField(max_digits=7, decimal_places=2)
    organizer = models.ForeignKey(Member, on_delete=models.CASCADE)
    booked_for = models.ManyToManyField(Member, related_name="booking_claims")

Now, in a view, I want to annotate my Members with infomation about the total spendings of a member (i.e. the sum of all prices of bookings where this member is the organizer) and the total claims from this member (i.e. the sum of all proportional costs of bookings where the member takes part in).

Ideally, this would work as follows:

members = Member.objects.annotate(
    total_spending=Sum("booking__price", default=0),
    total_claims=Sum(F("booking_claims__price") / Count("booking_claims")),
)

However, I get an error that Count is an aggregate and I cannot use Sum on an aggregate. I know, there exist some related questions and I have already tried a lot of different things, including subqueries and custom Count functions but I can't get it to work. Is there someone that knows how to solve this?

---

If I use a subquery e.g. like this:

    subquery = (
        Booking.objects.filter(spent_for=OuterRef("pk"))
        .annotate(
            total_claim=ExpressionWrapper(
                Cast(F("price"), FloatField()) / Count("booked_for"),
                output_field=DecimalField(),
            )
        )
        .values("total_claim")
    )

    members = Member.objects.annotate(
       total_claims=Sum(Subquery(subquery)),
    )

Then total_claims only holds the first value of the Queryset returned by the subquery (however, I want to sum up all the values returned by the subquery)

Answer 1

I found another (somewhat hacky) solution. I added a new field to my Booking model:

booked_for_count = models.PositiveIntegerField(editable=False, default=0, null=False, blank=False)

Then, I use an override of the save function of the ModelForm that I use to create and update bookings to set this additional field as follows:

def save(self, commit=True):
    self.instance.booked_for_count = self.cleaned_data["booked_for"].count()
    return super(BookingModelForm, self).save(commit)

Now, I am able to use the booked_for_count field in the annotation. However, it would be nice to know a more "correct" solution to that problem.