Re: Get latest item by many items in a queryset

On Fri, Sep 30, 2011 at 2:48 PM, John <theubaz@gmail.com> wrote:
> 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.
>

Sure you can:

SELECT backend.*, s1.*
FROM backend
INNER JOIN status s1
ON backend.id = s1.backend_id
LEFT JOIN status s2
ON s1.backend_id = s2.backend_id AND s2.timestamp > s1.timestamp
WHERE backend.id IN (1,2,3,4)
AND s2.id IS NULL

That will extract all the backend info and the status info for the
most recent timestamp in a single tuple. The trick is joining to the
status table twice and excluding all rows that have smaller
timestamps.

Remember that SQL is really very powerful, much more powerful than is
exposed by the ORM.

Cheers

Tom

--
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.