CODEWITHSUNDEEP
pythondjango
user1427661
user1427661

Reputation: 11774

Annotate with a filtered related object set

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

Upvotes: 1

Views: 493

Answers (3)

knbk
knbk

Reputation: 53659

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.

Upvotes: 1

Sahil kalra
Sahil kalra

Reputation: 9034

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

Upvotes: 1

Jorge Leitao
Jorge Leitao

Reputation: 20103

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.

Upvotes: 0

Related Questions