python - Django, how to make multiple annotate in single queryset? -

using django 1.7, python 3.4 , postgresql 9.1 having difficulties annotate on queryset. here model:

class payment(models.model):     type_choices= (         ('c', 'credit'),         ('d', 'debit')     )     amount = models.decimalfield(max_digits=8, decimal_places=2, default=0.0)     customer = models.foreignkey(customer, null=false)     type=models.charfield(max_length=1, null=true, choices=type_choices)  class customer(models.model):     name = models.charfield(max_length=100, unique=true)     available_funds = models.decimalfield(max_digits=8, decimal_places=2, null=true, default=0.0)     total_funds = models.decimalfield(max_digits=8, decimal_places=2, null=true, default=0.0) 

what trying like:

customers: name    | total in  | total out | available funds   | total funds ----------------------------------------------------------------- cust 1  | 255       | 220       | 5                 | 35 cust 2  | 100       | 120       | 0                 | -20 cust 3  | 50        | 20        | 15                | 30 

and data:

payments: amount  | customer  | type  --------------------------  20      | cust 1    | d  10      | cust 1    | c  70      | cust 2    | d  20      | cust 2    | c  10      | cust 2    | d  25      | cust 1    | c  200     | cust 3    | d  10      | cust 3    | c  20      | cust 1    | d  

i trying query set:

customer.objects.select_related().filter(q(payment__isnull=false)& q(payment__type='d')).values('name').annotate(sum('payment__amount')) 

but getting debits. don't know how create list customer,total in, total out, total funds, available funds.

can me this?

i think you're hitting limit of can single queryset. reason you're asking database aggregation on different sets of payment records.

let's @ current queryset:

customer.objects.select_related().filter(q(payment__isnull=false)& q(payment__type='d')).values('name').annotate(sum('payment__amount')) 

ignoring extraneous q() calls, filter call payment__type='d' means payment_amount pertain debits. if change 'c', it'll pertain credits. query demonstrates fundamental constraint imposed on django's queryset language -- can't generate 2 different aggregations , annotate them single record.

taking detour off raw sql land see how i'd write query way of demonstrating point. you'll note, of course, still running 2 different payment aggregations here! 1 credits , 1 debits.

select     *     customer         inner join             (                 select sum(amount) total payment type='c' group customer_id, type             ) credits             on credits.customer_id=customer.id         inner join             (                 select sum(amount) total payment type='d' group customer_id, type             ) debits             on debits.customer_id=customer.id 

that query return data approximately of form:

customer.id | customer.name | ... | credits.total | debits.total ----------------------------------------------------------------           1 |       foo bar |     | 20            | 30           2 |       baz qux |     | 30            | 20 

if try use 1 inner join/aggregation, you're forced have group both payment type , customer, resulting in table this:

customer_id | type | sum(amount) --------------------------------           1 | c    | 20           1 | d    | 30           2 | c    | 30           2 | d    | 20 

when inner join intermediate result customers, should clear debits , credits still not unified single record.

because can't sort of select inner joins in django (as far know), can't you're trying in single query. however, there solutions problem.

in descending order of desirability (in opinion, of course -- , based on consider obviousness/maintainability of resulting code), first multiple queries , unify results manually.

you can track credits/debits part of customer record. you're tracking available funds way (you're using f objects in query update/maintain these records, right?), it's not more onerous maintain credit/debit summaries in similar fashion well.

lastly, , don't think should don't think there's burning need to, can perform raw sql query results need in 1 go.