Django Code and Django Error Report Link
Given the three models as follows (simplified to demonstrate ... not really 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 the query set in the model administrator as follows. (This does not work due to this django error .)
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
An example of an unexpected database result
Executing annotations separately will display something like (with the removal of grouping and amounts)
+---------+--------+ | derp.id | r1_sum | +---------+--------+ | 2 | 500.00 | | 2 | 100.00 | +---------+--------+ r1_sum would be 600.00 and +---------+--------+ | derp.id | r1_sum | +---------+--------+ | 2 | 100.00 | | 2 | 250.00 | +---------+--------+ r2_sum would be 350.00
If you take qs.query with annotations turned on and delete the sums and grouping, then obviously the problem. In this case, we recount everything twice. Get more relationships, and we have an increasingly ugly increase in both columns of the sum.
+---------+--------+--------+ | 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 would incorrectly be 1200.00 r2_sum would incorrectly be 700.00
Question: Is there a route other than custom SQL?
I can write the query myself quite easily, but if someone has a suggestion that avoids writing custom SQL, that would be awesome.
Thanks for the help.
Edit: Here is a link to the annotations section of the Django documentation. One of the commentators mentioned a separate option. This does not work, and I believe that this is what is warned at the bottom of the annotation section in django's annotation documentation .
Edit2: The original idea of SQL is probably more complicated than I thought, since derp.objects.raw ('sql here') does not return the query object needed for use by the administrator. Is there a way to use two queries (a real set of queries plus a custom one doing the sums) and populate a list of both? One of the suggestions I found (which I can no longer find now: S) suggested creating a view that maps to the model definition, which is then set by unmanaged django (for syncdb). Then I could write my own code and specify it to be included in the original request. That sounds dirty. Thoughts?