Django Aggregation: Sum of Multiplication of two fields that are not in same model
<p>I have three models (Django 1.6.5):</p>

<pre><code>class Vote(models.Model):
    voter  = models.ForeignKey(UserSettings)
    answer = models.ForeignKey(Answer)
    rating = models.IntegerField()

class Answer(models.Model):
    content = models.CharField(max_length=255)

class UserSettings(models.Model): 
    user = models.OneToOneField(User, related_name='settings')
    weight = models.FloatField(default=1.0)
</code></pre>

<p>Basically, a user (voter) can vote for an answer by giving a rating. I know how to sum the ratings by answer:</p>

<pre><code>Vote.objects.all().values('answer').annotate(score=Sum('rating'))
</code></pre>

<p>The only subtlety is that each voter has a weight (all voters are not equal!) and I want to sum each product rating*weight. I know (from <a href="http://stackoverflow.com/questions/12165636/django-aggregation-summation-of-multiplication-of-two-fields">here</a>) that something like that can be done:</p>

<pre><code>Sum('id',field="field1*field2")
</code></pre>

<p>and it would work well if my 2 fields are in the same model but it doesn't work if they are not. In other words, command:</p>

<pre><code>Vote.objects.all().values('answer').annotate(score=Sum('id',field="rating*voter__weight"))
</code></pre>

<p>does not work. Any help greatly appreciated!</p>

<p>The problem is that we need the join with another table (in this case UserSettings), so we need "force" the join.</p>

<pre><code>q = Vote.objects.all().filter(voter__settings__weight__gt=0).values("answer").annotate(Sum('id', field='rating*weight'))
</code></pre>

<p>To force the join I used the filter clause (In fact I suppose that all the users have a weight greater than 0) but is just used to force the join. Then you can use the weight field.</p>

<p>PD: I think that this problem is solved in the latest version of Django with Conditional Expressions: <a href="https://docs.djangoproject.com/es/1.10/ref/models/conditional-expressions/" rel="nofollow">https://docs.djangoproject.com/es/1.10/ref/models/conditional-expressions/</a></p>

python django aggregate django-queryset