Re: Get latest item by many items in a queryset

I believe that there is no real way for you to avoid having to perform
a query for each backend instance. Even in raw SQL, I don't think
there is a way to do what you want - in postgres, for example, you
could not both order by the timestamp and get distinct values based on
backend.

However, there is an alternative solution - you can make one query and
request all statuses from the past 10 minutes, then manually find the
most recent statuses per backend. For instance:

from datetime import datetime, timedelta

backends = Backend.objects.all()
statuses = Status.objects.filter(timestamp__gt=(datetime.now()-
timedelta(minutes=10))).order_by(timestamp).only('backend')

mapping = {}

for st in statuses:
mapping[st.backend_id] = st

print 'The following have had no status update in the past 10 minutes:
'
print '\n'.join(map(str, filter(lambda x:(x.id not in mapping),
backends)))

Depending on how many status updates you make per 10 minute period on
each backend, this could have very low (or very high) overhead on
database IO and webserver CPU usage.

On Sep 29, 7:36 pm, Colin <cwoo...@gmail.com> wrote:
> Hi Users,
>
> So I have a DB that has a list of backends and there properties and I
> have a table that gets updated with if it is able to access and the
> status. It is only updated when it is reported down or, if it was
> reported down and is currenly back up.
>
> What i want to do is make a queryset that will get only the latest
> status for a list of backends.
>
> #models.py
>
> class Backend(models.Model):
>         src_host = models.CharField(max_length=255)
>         ip = models.CharField(max_length=45)
>         port = models.CharField(max_length=8)
>         endpoint_name = models.CharField(max_length=255)
>         service_prop_name = models.CharField(max_length=255)
>         endpoint_url = models.CharField(max_length=255)
>
> class Status(models.Model):
>         backend = models.ForeignKey(Backend)
>         timestamp = models.DateTimeField()
>         status = models.CharField(max_length=255)
>
>         class Meta():
>                 get_latest_by = 'timestamp'
>
> What I would like to do is get the latest status reported for the last
> ten minutes. Without having to loop through a list querysets and
> performing the .latest() function on it.
>
> Is this possible?
>
> Thanks,
>
> Colin

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.