Suriya, You will probably have to do this in custom SQL or using extra(). Your query requires a subselect to get the "current B's" (B's with max(date) for each A).
Here's the SQL that I think you need (if I understand the problem correctly): select * from <app>_A join <app>_B on <app>_B.a_id = <app>_A.id where <app>_B.date = (select max(date) from <app>_B where a_id = <app>_B.a_id) and <app>_B.status = 1 I think that maps to A.objects.extra(where=['<app>_B.a_id = (select max(date) from <app>_B where a_id = <app>_B.a_id)'], tables=['<app>_B']).filter(b__status=1) You might want to consider modeling this differently. I have a similar problem where I'm essentially tracking different versions of an object. But instead of just using one date, I use two for the range that the version was valid: date_from and date_thru. For the current version, I set date_thru to null. Then a query of the current versions is really easy: filter(date_thru__isnull=True). You can also see all versions at a given point in time with the slightly more complex (but efficient): filter(date_from__lte=some_date).filter(Q(date_thru__gt=some_date)|Q(date_thru__isnull=True)) In your design all of these types of queries require a subselect. Of course, its more work to keep my table up to date, but I have the need to query it arbitrarily in many ways so paying a little expense at insert time (once per quarter) for better query performance (many times per day) is a good tradeoff, in my case. -Dave Suriya wrote: > SmileyChris wrote: > > How about just making the query like this: > > > > A.objects.filter(b__status=1) > > This returns the list of rows in table A that have status > in table B set to 1 at some point in the past. What I want > is the latest status from table B. For example, if table B > has the two entries: > B(id=1, a=1, status=1, date=yesterday) and > B(id=2, a=1, status=0, date=today) > a = 1 should not be returned in the list, because the current > status is 0. > > Suriya --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users -~----------~----~----~----~------~----~------~--~---
