The title might be misleading, I'm not sure on how to express my problem
I have two models declared this way:
class Record(models.Model):
# autogen id as primary key
name = models.ForeignKey(Product, on_delete=models.DO_NOTHING)
value = models.FloatField()
date = models.DateTimeField()
class Integration(models.Model):
# autogen id as primary key
action = models.FloatField()
on_record = models.ForeignKey(Record, on_delete=models.DO_NOTHING)
I have one Record per hour per day on an entire year, thus 24*365.
For each Record, there are exactely ten (10) Integrations, each having their distinct action.
What I need is a QuerySet on Record that has an "extra column" with the average of the related 10 Integration's actions. So
| id | name | value | date |
|---|---|---|---|
| 1 | A | 120.00 | 2020-01-01 00:00 |
| 2 | B | 85.00 | 2020-01-01 01:00 |
| id | action | on_record |
|---|---|---|
| 0x1 | 7.00 | 1 |
| 0x2 | 2.00 | 1 |
| 0x3 | 9.00 | 1 |
| ... | ... | ... |
| 0x10 | 8.41 | 1 |
| 0x11 | 8.99 | 2 <-- next Record |
| id | name | value | date | integration_avg_action |
|---|---|---|---|---|
| 1 | A | 120.00 | 2020-01-01 00:00 | 8.17 |
| 2 | B | 85.00 | 2020-01-01 00:00 | 7.58 |
I was able to generate the average of the Integrations this way
Integration.objects
.values('on_record')
.annotate(avg=Avg('action'))
but this is as far as I've come to. I've tried itertools.chain and many select_related() but I'm more confused now than before. It looks to me as a simple JOIN but I can't find a way to get it via ORM.
Since you want a QuerySet of Record you should annotate on that instead of on Integration. See the section Following relationships backwards in the documentation for aggregation.
Record.objects.annotate(integration_avg_action=Avg('integration__action'))
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments