Django ManyToMany trhough summary calculation
Giving next models:
class Question(models.Model): question = models.TextField() output_params = models.ManyToManyField(OutputParam, through='OutputParamWeight') class OutputParam(TimeStampModel): form = models.ForeignKey(Form, on_delete=models.CASCADE, related_name='output_params') name = models.CharField(max_length=255) class OutputParamWeight(models.Model): output_param = models.ForeignKey(OutputParam, on_delete=models.CASCADE) question = models.ForeignKey(Question, on_delete=models.CASCADE) weight = models.IntegerField() class Answer(TimeStampModel): question = models.ForeignKey(Question, on_delete=models.CASCADE, related_name='answers') value = models.IntegerField()
Every question affects N output params in a form multiplying x1, x2, x3 the associates answer value. For example:
Question 1 -> Output param 1 x1 Output param 2 x3 Output param 3 x2 Question 2 -> Output param 1 x2 Output param 3 x3 Answer 1 -> Question 1 Value 2 Answer 2 -> Question 2 Value 3
So, what I'm trying to do now is summarize the form result as follows:
Output param 1: 6 (answer1.value * question1.output_param1.weight + answer1.value * question2.output_param1.weight) Output param 2: 15
Which queries should I perform to get the final result in an optimized way?