Django query sum values from related table

Question

I have two tables:

Ticket Table

id  paid_with_tax
1   5
2   6
3   7

TicketAdjustment Table

id  ticket_id  value_with_tax
1   1          2
2   1          1
3   1          2
4   1          3
5   2          5

The query I use:

use = 0

Ticket.objects.all().annotate(
   paid_amount=Sum(
        F('paid_with_tax') +
        Coalesce(F('ticketadjustment__value_with_tax'), 0) * use
    )
)

the query would return the following:

[
    {id: 1, paid_amount: 7},
    {id: 1, paid_amount: 6},
    {id: 1, paid_amount: 7},
    {id: 1, paid_amount: 8},
    {id: 2, paid_amount: 11},
    {id: 3, paid_amount: 7},
]

but the above is incorrect since the Ticket Table id=1 values are duplicated by the TicketAdjustment Table values.

how can i get the query to sum the TicketAdjustment Table values and return the following:

[
    {id: 1, paid_amount: 13},
    {id: 2, paid_amount: 11},
    {id: 3, paid_amount: 7},
]

Wilfried · Accepted Answer · 2017-02-16 14:36:34Z

Here the solution for your problem :

Ticket.objects.all().annotate(
   paid_amount=(F('paid_with_tax') +
        Sum(Coalesce(F('ticketadjustment__value_with_tax'), 0))
    )
).values_list('id', 'paid_amount')

values_list select the field you want in your result.

In your primary request there is a big problem.

Sum(F('paid_with_tax') + Coalesce(F('ticketadjustment__value_with_tax'), 0) * use)

This ligne miltiply value_with_tax with zero. So give you zero. It's like :

Sum(F('paid_with_tax'))

You want the sum of value_with_tax for each ticket, this is why I move Sum on it :

Sum(Coalesce(F('ticketadjustment__value_with_tax'), 0))

And after add the value of paid_with_tax

NB : I remove your variable use, because don't know is goal.

Collectives™ on Stack Overflow

Django query sum values from related table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related