Re: Get latest timestamp+value from each group

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?

On Wednesday, July 30, 2014 2:11:49 PM UTC-5, Joshua Lyon wrote:

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!

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/07f467fa-1d97-4bd3-a2a0-fe520ce01395%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.