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+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
