Home > Software engineering >  Summing columns in Django with related fields
Summing columns in Django with related fields

Time:12-19

I'm trying to do some aggregation on some of my Django columns, but I'm getting an error I can't figure out. Here are my models (the relevant stuff anyways):

class Fillup(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL,on_delete=models.CASCADE)
    date = models.DateField(default=date.today)
    trip_distance = models.FloatField(validators=[MinValueValidator(0.0)])
    car = models.ForeignKey('Car',on_delete=models.CASCADE, related_name='fillups')


class Car(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL,on_delete=models.CASCADE)
    name = models.CharField(max_length=25)

So each Fillup object is essentially a tank of gas in a car, and what I want to do is get the total of the trip_distance column for each car. I've tried several things from various other StackOverFlow pages, but nothing seems to work.

My first attempt was this adding this field to the Car Model:

@property
def distance_driven(self):
    return self.fillups.aggregate(sum('trip__distance'))

But I just get the following error:

TypeError: unsupported operand type(s) for  : 'int' and 'str'

I also tried adding this to my serializer, but got the same error:

distance_driven = serializers.SerializerMethodField()

def get_distance_driven(self, ob):
    return ob.fillups.all().aggregate(sum('trip_distance'))['trip__distance']

Is there something I'm missing here? I don't understand why I'm getting that error when trying to sum a FloatField column.

Any help would be greatly appreciated!

CodePudding user response:

In order to aggregate, you use a Sum object [Django-doc], not the Python builtin sum.

You can obtain the sum of a single object with:

from django.db.models import Sum

return self.fillups.aggregate(total=Sum('trip_distance'))['total']

If you have to do this for multiple records, it is better to .annotate(…) [Django-doc]:

from django.db.models import Sum

Car.objects.annotate(
    total_distance=Sum('fillups__trip_distance')
)

The Car objects that arise from this queryset will have an extra attribute .total_distance with the sum of the related Fillup trip_distances.

  • Related