How to get a models with highest value grouped by related model in django?

Question

I would like to perform this query in django:

For each sensor, select it's latest message

In SQL it would be something like

SELECT * FROM (SELECT * FROM messages order by date_entered DESC) as order GROUP BY sensor_id

Models are defined like this

class BaseModel(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    date_entered = models.DateTimeField(auto_now_add=True)
    date_modified = models.DateTimeField(auto_now=True)
    deleted = models.IntegerField(default=0)

    class Meta:
        abstract = True
        ordering = ['date_modified']

class Device(BaseModel):

    user =              models.ForeignKey(User, on_delete=models.SET_NULL, blank=True, null=True)

    name =              models.CharField(max_length=255, blank=False)
    identifier =        models.CharField(max_length=31, blank=False)
    imei =              models.CharField(max_length=31, blank=False)
    status =            models.CharField(max_length=31, blank=False)
    settings =          models.TextField()


class DeviceMessage(BaseModel):


    device =            models.ForeignKey(Device, on_delete=models.SET_NULL, blank=True, null=True)
    user =              models.ForeignKey(User, on_delete=models.SET_NULL, blank=True, null=True)

    latitude =          models.DecimalField(max_digits=9, decimal_places=6, blank=True, null=True)
    longitude =         models.DecimalField(max_digits=9, decimal_places=6, blank=True, null=True)
    altitude =          models.DecimalField(max_digits=7, decimal_places=2, blank=True, null=True)
    signal_strength =   models.DecimalField(max_digits=5, decimal_places=2, blank=True, null=True)
    battery =           models.DecimalField(max_digits=5, decimal_places=2, blank=True, null=True)

    satellites =        models.IntegerField(blank=True, null=True)
    size =              models.IntegerField(blank=True, null=True)

    raw =               models.TextField(blank=True, null=True)

Is it possible to achieve this in django?

Basically, it is this problem Using ORDER BY and GROUP BY together

basically, it is this problem https://stackoverflow.com/questions/10030787/using-order-by-and-group-by-together — Charlestone, Mar 08 '19 at 13:05
of course it's possible, in fact very straightforward. Providing you have a relationship between your two models (probably a `ForeignKey`). We need to see what your models look like of course. — Robin Zigmond, Mar 08 '19 at 13:13
Thanks. I notice your models don't all extend directly from `models.Model`, but from your `BaseModel`. I've never actually done this myself, and would suggest you read the documentation [here](https://php7.forskills.co.uk/portfolio/uploadportfoliotasks.php) if you intend to do it this way. (In particular, if - as seems likely - your `BaseModel` only exists to save you manually adding those fields to each of your models, and shouldn't represent its own database table, you probably want to make it an Abstract base class.) — Robin Zigmond, Mar 08 '19 at 13:48
Yes, the class is abstract, I copied only fields definitions — Charlestone, Mar 08 '19 at 13:54

score 1 · Accepted Answer · edited Mar 08 '19 at 16:20

Since Django 1.11 there is the Subquery feature that you can use to annotate data:

from django.db.models import OuterRef, Subquery

latest = DeviceMessage.objects.filter(device_id=OuterRef('pk')).order_by('-date_entered')

devices = Device.objects.annotate(latest_message_id=Subquery(latest.values('pk')[:1]))
message_ids = [d.latest_message_id for d in devices]
for message in DeviceMessage.objects.filter(pk__in=message_ids).select_related('device'):
    print(device.name, message)

......

score 0 · Answer 2 · answered Mar 08 '19 at 14:02

0

"For each sensor, select it's latest message"

I assume by "sensor" you are referring to the Device model. In that case (since you've now clarified that BaseModel was an abstract base class), it is as simple as this (with sensor being the particular Device instance you're querying about):

sensor.devicemessage_set.order_by("-date_entered")[0]

If you want to find this information for multiple sensors at once, you might be better off using an annotation, for example:

from django.db.models import Max
...
Device.objects.all().annotate(latest_msg=Max("devicemssage_set__date_entered"))

This gives each Device object a (temporary) property called latest_msg which holds the information you want.

answered Mar 08 '19 at 14:02

Robin Zigmond

17,805
2
23
34

I need it for all the sensors/devices at once. The annotation will return only the date_entered value, right? I need to work with the whole message object (latitude and longitude is what I want to get) – Charlestone Mar 08 '19 at 14:09
Well the first of my 2 code snippets there is a reference to the whole object corresponding to the latest message - from which you can then access all fields. If this still doesn't answer your question then I think I would need to know more detail about what you actually need. – Robin Zigmond Mar 08 '19 at 14:18
Well, I understand the first snippet will give me what I want, but only for one sensor. To get all the messages, I would have to iterate trough all the sensors (possibly hundreds of thousands) and it just does not seem right. Or is there not a problem with it? It would be really nice to get the results in one query, instead of iterating through all the sensor. – Charlestone Mar 08 '19 at 14:22
Well that's a good question - unfortunately I'm not sure I can answer it. (Not saying there isn't an answer, just that my own Django knowledge isn't yet at the level where I could.) I suppose at worst you could fall back on using [raw SQL](https://docs.djangoproject.com/en/2.1/topics/db/sql/) (although this definitely isn't recommended). I must say though I'm suspicious of any web application which needs to render "hundreds of thousands" of records on a single page - at the very least you should think about using pagination. – Robin Zigmond Mar 08 '19 at 14:57
I am using the django platform as a rest api. I am not rendering anything, just sending last messages of registered sensors :) and of course there will by additional filters, like "show only latest messages from last hour", or filter for coordinates. – Charlestone Mar 08 '19 at 15:17
And that is exactly why I don't want to use raw sql, because I would like to use djangos default pagination – Charlestone Mar 08 '19 at 15:18

How to get a models with highest value grouped by related model in django?

2 Answers2