CODEWITHSUNDEEP
pythondjango
davide m.
davide m.

Reputation: 462

django ORM join two model on a foreign key

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

Record

id name value date
1 A 120.00 2020-01-01 00:00
2 B 85.00 2020-01-01 01:00

Integration

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

What I need

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.

Upvotes: 0

Views: 149

Answers (2)

Neeraj
Neeraj

Reputation: 997

If at all changing your models is not costly then you can add related name to your FK field:

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, related_name='integration')

Then you can execute the following query:

Record.objects.all().annotate(integration_avg_action=Avg('integration__action')).values('name', 'value', 'date', 'integration_avg_action')

Upvotes: 0

Abdul Aziz Barkat
Abdul Aziz Barkat

Reputation: 21787

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'))

Upvotes: 3

Related Questions