Django annotate sum on fk_set

Question

I'm trying to annotate sum of fields in related set:

My models:

class Report(models.Model):

class ReportCommissionPosition(models.Model):
    report = models.ForeignKey(Report)
    reservation = models.OneToOneField("reservations.Reservation")

class Reservation(models.Model):

class Payment(models.Model):
    amount = models.DecimalField(max_digits=10, decimal_places=2)
    reservation = models.ForeignKey('reservations.Reservation')
    PAYMENT_TYPES=(
        ('TRANS', 'TRANS'),
        ('SELL', 'SELL'),
    payment_accounting_type = models.CharField(max_length=50, choices=PAYMENT_TYPES)

I need to annotate each position of report.reportcommissionposition_set with two fields:

trans_amount - Sum of all the payments with payment accounting type == 'SELL' (through reservation => payment) sum_amount - Sum of all the payments with payment accounting type == 'TRANSFER' (through reservation => payment)

I have tried:

for position in commission_position:
    position.sell_pay = position.reservation.payment_set.filter(payment_accounting_type='SELL').aggregate(amount=Sum('amount'))['amount']

But this creates new query for every element.

Any thoughts?

willeM_ Van Onsem · Accepted Answer · 2021-08-28 06:13:08Z

3

You can annotate these with:

myreport.reportcommissionposition_set.annotate(
    trans_amount=Sum(
        'reservation__payment__amount',
        filter=Q(reservation__payment__type='SELL')
    ),
    sum_amount=Sum(
        'reservation__payment__amount',
        filter=Q(reservation__payment__type='TRANS')
    )
)

the ReportCommissionPosition objects of myreport will have two extra fields: .trans_amount and .sum_amount with the sums of the SELL and TRANS payments respectively.

answered Aug 28, 2021 at 6:13

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kihaf Over a year ago

Thank you Willem for sharing you knowledge! You made my day! I really apprecciate this! Optimisation note for anyone interested: I have added select_related('reservation') to the query, to reduce number of db queries

Collectives™ on Stack Overflow

Django annotate sum on fk_set

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related