Get latest timestamp+value from each group

[Joshua Lyon]

I have a basic Django application which uses sqlite as a backend on low-end hardware (eg. Raspberry Pi). I have the RPi collecting sensor data and logging it. I am trying to get the last process value, timestamp, and associated sensor name in an efficient way, but can't seem to figure it out with the Django ORM.

models.py

class Device(models.Model):
    name = models.CharField(max_length=30)


class Log(models.Model):
    timestamp = models.IntegerField()
    value = models.FloatField()

    device = models.ForeignKey(Device)


    class Meta:
        unique_together = ("timestamp", "device")

The following works, but is inefficient as it requires multiple SQL calls (one to start and then one for each device):

devices = Device.objects.all()

series = []
for device in devices:
    log = Log.objects.filter(mapping_id=device.id).latest("timestamp")
    series.append({"name": device.name,
                   "timestamp": log.timestamp,
                   "value": log.value})

I have searched around and it seems the annotate() method might be able to help, but I can't seem to figure out how to get the associated value along with the max timestamp:

Log.objects.values("device__name").annotate(timestamp=Max("timestamp"))  # missing log value!
  # or
Device.objects.annotate(timestamp=Max("log__timestamp"))  # missing log value!

[Joshua Lyon]

I would also add that using the Max ID as the annotation field for the device (group) is what I am using in the interim, but it only works since currently my log IDs are automatically generated and each newly entered timestamp is younger than the previous. My concern is that I would like to extend the application to allow the possibility of backfilling data (entering old timestamps) in which case the Max IDs are no longer a good proxy for the Max timestamps.

devices = Device.objects.annotate(max_log_id=Max("log__id"))
logs = Log.objects.filter(id__in=[device.max_log_id for device in devices])

Any thoughts or suggestions?

[Collin Anderson]

I'm not an ORM/SQL pro myself, so if I were doing this, I would just cache/denoralize the last value onto the device whenever there's a new log.

[Joshua Lyon]

Thanks for the feedback. I'm not sure I would want to change the Device object directly, but I might consider a separate Snapshot object which keeps record of the latest Log object per Device. If I go down that route, I think I'll have to analyze a bit more between a One-to-One relationship (linked) or Model Inheritance (copied) and the performance/resource trade offs of each.

[Collin Anderson]

Or if you have a "latest" boolean, that would also work.