This query is harder than it looks at first glance. AFAIK the Django ORM does not provide any way to generate efficient SQL for this query, because the efficient SQL requires a correlated subquery. (I'd love to be corrected on this!) You can generate some ugly SQL with this query:
Projectfundingdetail.objects.annotate(latest=Max('project__projectfundingdetail__end_date')).filter(end_date=F('latest')).filter(budget__lte==1000).select_related()
But this requires to join from Projectfundingdetail to Project and back again, which is inefficient (though perhaps adequate for your needs).
The other way to do this is to write raw SQL and encapsulate it in a manager method. It looks a little bit scary but works great. If you assign the manager as "objects" attribute on Projectfundingdetail, you can use it like this to get the latest funding details for each project:
>>> Projectfundingdetail.objects.latest_by_project()
And it returns a normal QuerySet, so you can add on further filters:
>>> Projectfundingdetail.objects.latest_by_project().filter(budget__lte=1000)
Here's the code:
from django.db import connection, models
qn = connection.ops.quote_name
class ProjectfundingdetailManager(models.Manager):
def latest_by_project(self):
project_model = self.model._meta.get_field('project').rel.to
names = {'project': qn(project_model._meta.db_table),
'pfd': qn(self.model._meta.db_table),
'end_date': qn(self.model._meta.get_field('end_date').column),
'project_id': qn(self.model._meta.get_field('project').column),
'pk': qn(self.model._meta.pk.column),
'p_pk': qn(project_model._meta.pk.column)}
sql = """SELECT pfd.%(pk)s FROM %(project)s AS p
JOIN %(pfd)s AS pfd ON p.%(p_pk)s = pfd.%(project_id)s
WHERE pfd.%(end_date)s =
(SELECT MAX(%(end_date)s) FROM %(pfd)s
WHERE %(project_id)s = p.%(p_pk)s)
""" % names
cursor = connection.cursor()
cursor.execute(sql)
return self.model.objects.filter(id__in=[r[0] for r
in cursor.fetchall()])
About half of that code (the "names" dictionary) is only necessary to be robust against the possibility of nonstandard database table and column names. You could also just hardcode the table and column names into the SQL if you're confident they won't ever change.