python - Django aggregation across multiple tables in ModelAdmin queryset -

django code & reference django bug report

given 3 models follows (simplified excessively demonstration...not identical related models)

class derp(models.model):     ... class derp_related_1(models.model):     fk = models.foreignkey(derp)     amount = models.decimalfield(max_digits=15, decimal_places=2) class derp_related_2(models.model):     fk = models.foreignkey(derp)     amount = models.decimalfield(max_digits=15, decimal_places=2) 

and overriding queryset in model admin follows. (it isn't working because of this django bug.)

class derpadmin(admin.modeladmin):     ...     list_display = ['derp_r1_sum', 'derp_r2_sum']     ...     def queryset(self, request):         qs = super(derpadmin, self).queryset(request)         qs = qs.annotate(derp_r1_sum=models.sum('derp_r1__amount', distinct=true))         qs = qs.annotate(derp_r2_sum=models.sum('derp_r2__amount', distinct=true))     def derp_r1_sum(self, obj):         return u'%s' % obj.derp_r1_sum     def derp_r2_sum(self, obj):         return u'%s' % obj.derp_r2_sum 

example of unexpected database result

running annotations individually render (with grouping & sums removed)

+---------+--------+ | derp.id | r1_sum | +---------+--------+ |       2 | 500.00 | |       2 | 100.00 | +---------+--------+ r1_sum 600.00 , +---------+--------+ | derp.id | r1_sum | +---------+--------+ |       2 | 100.00 | |       2 | 250.00 | +---------+--------+ r2_sum 350.00 

if take qs.query both annotations included , remove sums , grouping obvious problem is. in case we're counting twice. more relations , have increasingly ugly increase in both sum columns.

+---------+--------+--------+ | derp.id | r1_sum | r2_sum | +---------+--------+--------+ |       2 | 500.00 | 100.00 | |       2 | 500.00 | 250.00 | |       2 | 100.00 | 100.00 | |       2 | 100.00 | 250.00 | +---------+--------+--------+ r1_sum incorrectly 1200.00 r2_sum incorrectly 700.00 

question, there route other custom sql?

i can write query myself easy enough, if has suggestion avoid writing of custom sql awesome.

thanks help.

edit: here link annotations section of django documentation. 1 commenter mentioned distinct option. not work, , believe warned @ bottom of annotation section in django documentation on annotation.

edit2: raw sql idea more difficult thought derp.objects.raw('sql here') not return queryset object necessary admin use it. there way use 2 queries (the real queryset plus custom 1 doing sums) , populate listview both? 1 suggestion found (which cannot find again :s) suggested creating view maps model definition set unmanaged django (for syncdb). write custom code, , reference inclusion in original query. sounds messy. thoughts?

if want stay within django's queryset, consider creating model superclass shares related , common fields , sub-classing further distinctions. otherwise, need either write custom sql or out of database orm entirely , manipulate data in python queryset.values or queryset.values_list