Writing nested queries in Django

Question

I am trying to write a nested query in Django. It is very simple to do in SQL, but with Django I am having some trouble deciding if I am doing it right or not. I have three models. Area, Location, and Measurement.

Area

class Area(models.Model):
    name = models.CharField(max_length=200)
    longitude = models.FloatField()
    latitude = models.FloatField()

Location

class Location(models.Model):
    name = models.CharField(max_length=200)
    altitude = models.IntegerField()
    area = models.ForeignKey(Area, on_delete=models.CASCADE)

Measurement

class Measurement(models.Model):
    value = models.FloatField()
    date = models.DateTimeField()
    location = models.ForeignKey(Location, on_delete=models.CASCADE)

Inside Area, I need a function that will return the average of the measurements for this area. So basically, I need the average measurements of all locations for this area. Area can have many locations, but locations can have one area. In the Area Model, I made this function:

def average_measurement(self):
    all_locations = self.location_set.all()
    return all_locations.measurement_set.all().aggregate(Avg('value'))

This is my equivalent to writing a nested query in Django. I get all locations first and then find the average of all their measurements. Am I doing this correctly?

On a side question, would this query be equivalent to doing something like this:

avg = 0
locations = self.location_set.all()
sum = 0
counter = 0
for l in locations: measurement = l.measurement_set.all()
    if measurement:
        for m in measurement:
           sum += m.value
           counter += 1
if counter != 0:
    avg = sum / counter
return avg

VMatić · Accepted Answer · 2017-12-10 03:47:58Z

2

When working with a list of objects, it is generally seen as more readable to query the given model directly. So you could instead use:

def average_measurement(self):
    return Measurement.objects.filter(location__area = self).aggregate(Avg('value'))['value__avg']

You could also adopt this approach in your second example:

avg = 0
sum = 0
counter = 0
for m in Measurement.objects.filter(location__area = self):
    sum += m.value
    counter += 1
if counter != 0:
    avg = sum / counter
return avg

answered Dec 10, 2017 at 3:47

VMatić

9962 gold badges10 silver badges19 bronze badges

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

5 Comments

user2896120 Over a year ago

I just have a couple questions. Your approach is more readable, but is my approach correct? And why do you add ['value__avg'] at the end?

VMatić Over a year ago

Your approach would work because it would be generating the same list of Measurement objects. I added the ['value__avg'] at the end in order to return the value directly, as the aggregate() function returns a dictionary. If you want the dictionary though (i.e if you were generating multiple aggregates and wanted to return them all), you could remove that part.

user2896120 Over a year ago

Thanks! Out of curiosity which do you think has the best performance, my approach, your approach, or the for loop approach?

VMatić Over a year ago

I think our two approaches would be the same, as I think Django would be executing the same number of queries (maybe even the same query?). However I think the loop approach would be slower as my understanding is that aggregate() uses database level APIs to do the calculation, which would be faster than Python code.

Satendra Over a year ago

@user2896120 your approach is lengthy and hence it will decrease performance, firstly you are fecthing all location after that all mesurment using reverse relation all_locations.measurement_set.all(). your code making multiple database query.

Collectives™ on Stack Overflow

Writing nested queries in Django

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related