Re: Get latest item by many items in a queryset

Thanks for the info guys!

Is there any changes I could make to my model? Right now I have about
165k backends and a huge number of statuses. But they are small if you
only grab the last 10 or so minutes of statuses.

On Sep 30, 10:43 am, Tom Evans <tevans...@googlemail.com> wrote:
> On Fri, Sep 30, 2011 at 2:48 PM, John <theu...@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.