how to use django annotate with foreign key

Question

Consider simple Django models

class Journey(models.Model):
    vrn=models.CharField(max_length=200) # Vehicle Reg No
    kilo=models.FloatField()

class J_user(models.Model):
    jdi=models.ForeignKey(Journey, related_name="Journey_User",on_delete = models.DO_NOTHING,)
    uid=models.IntegerField()

It's easy to annotate in a single table like if we want sum total driven kilometers for each vehicle (vrn represent registration number of the vehicle)

Journey.objects.values('vrn').annotate(Total_kilo=Sum('kilo'))

Now i want to make a query that will return how many kilometers each user has traveled in each car.

Let Data of Journey table

Data of J_user table

Then the result should be

Thanks for your help.

dani herrera · Accepted Answer · 2018-11-16 18:20:50Z

2

This is your query:

Journey
.objects
.order_by()   #<-- important to avoid include sort fields
.values('vrn', 'j_user__uid', )
.annotate(Total_kilo=Sum('kilo'))

Fields on values will be included on the aggregation clause. Sample:

print(
    Material
    .objects
    .values( "uf_id", "uf__mp__id", )
    .annotate( Sum("total_social_per_c") )
    .query )

Result:

SELECT "material_material"."uf_id", 
       "ufs_uf"."mp_id", 
       Sum("material_material"."total_social_per_c") AS 
       "total_social_per_c__sum" 
FROM   "material_material" 
       INNER JOIN "ufs_uf" 
               ON ( "material_material"."uf_id" = "ufs_uf"."id" ) 
GROUP  BY "material_material"."uf_id", 
          "ufs_uf"."mp_id"

answered Nov 16, 2018 at 18:20

dani herrera

52k11 gold badges133 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ivissani · Accepted Answer · 2018-11-16 18:22:09Z

0

According to your models it should be:

J_user.objects.values('uid', vrn=F('jdi__vrn')).annotate(kilo=Sum('jdi__kilo'))

answered Nov 16, 2018 at 18:22

ivissani

2,6641 gold badge20 silver badges12 bronze badges

Collectives™ on Stack Overflow

how to use django annotate with foreign key

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related