Annotate with a filtered related object set

Question

So I have a SensorType model, which has a collection of SensorReading objects as part of a sensorreading_set (i.e., the sensor type has many sensor readings). I want to annotate the sensor types to give me the sensor reading with the max id. To wit:

sensor_types = SensorType.objects.annotate(
                newest_reading_id=Max('sensorreading__id'))

This works fantastically, but there's a catch. Sensor Readings have another foreign key, Device. What I really want is the highest sensor reading id for a given sensor type for a given device. Is it possible to have the annotation refer to a subset of sensor readings that basically amounts to SensorReading.objects.filter(device=device)?

Uhh, can't you just chain those two? `SensorReading.objects.filter(device=device).annotate(newest_reading_id=Max('sensorreading__id'))`? — Two-Bit Alchemist, Apr 29 '14 at 22:17
You're forgetting the Sensor Type. I need to get the max id sensor reading for each type, not the max for every sensor reading, so I can't simply chain using the SensorReading resource. — user1427661, Apr 29 '14 at 22:21
Ahh, you're right! I conflated your models, didn't I? You probably need a [Q object](https://docs.djangoproject.com/en/dev/topics/db/queries/#complex-lookups-with-q-objects) for this kind of query. — Two-Bit Alchemist, Apr 29 '14 at 22:23

score 1 · Answer 1 · answered Apr 30 '14 at 05:57

You can use .extra for these type of Queries in Django:

Like this:

SensorType.objects.extra(
     select={
       "newest_reading_id": "SELECT MAX(id) FROM sensorreading WHERE sensorreading.sensortype_id = sensortype.id AND sensorreading.device_id=%s",
     },
     select_params = [device.id]
)

You can read more about .extra here : https://docs.djangoproject.com/en/1.6/ref/models/querysets/#django.db.models.query.QuerySet.extra

knbk · Accepted Answer · 2014-04-30T22:06:10.560

Filtering works perfectly fine with related objects, and annotations work perfectly fine with those filters. What you need to do is:

from django.db.models import Max

SensorType.objects.filter(sensorreading__device=device) \
                  .annotate(newest_reading_id=Max('sensorreading__id'))

Note that the order of function calls matters. Using filter before annotate will only annotate on the filtered set, using annotate before filter will annotate on the complete set, and then filter. Also, when filtering on related objects, keep in mind that filter(sensorreading__x=x, sensorreading__y=y) will filter for sensorreadings where all conditions are true, while .filter(sensorreading__x=x).filter(sensorreading__y=y) will filter for sensorreadings where either one of these conditions is true.

score 0 · Answer 3 · edited May 23 '17 at 12:20

0

As I understood, you want to GROUP_BY on two fields, device_id and sensortype_id. This can be done using:

SensorReading.objects.all().values('device_id', 'sensortype_id').annotate(max=Max('id'))

I didn't tried it; it was taken from 2 different answers in SO, this one and this one.

edited May 23 '17 at 12:20

Community

1
1

answered Apr 30 '14 at 04:59

Jorge Leitao

19,085
19
85
121

Annotate with a filtered related object set

3 Answers3