optimal django manytomany query

Question

I'm having trouble reducing the number of queries for a particular view. It's a fairly heavy one but I'm sure it can be reduced:

Profile:
  name = CharField()

Officers:
  club= ManyToManyField(Club, related_name='officers')
  title= CharField()

Club:
  name = CharField()
  members = ManyToManyField(Profile)

Election:
    club = ForeignKey(Club)
    elected = ForeignKey(Profile)
    title= CharField()
    when = DateTimeField()

Clubs have members and officers (president, tournament director). People can be members of multiple clubs etc... Officers are elected at elections, the results of which are stored.

Given a player how can I find out the most recently elected officer at each of the players clubs?

At the moment I have

clubs = Club.objects.filter(members=me).prefetch_related('officers')
for c in clubs:
  officers = c.officers.all()

  most_recent = Elections.objects.filter(club=c).filter(elected__in=officers).order_by('-when')[:1].get()
  print(c.name + ' elected ' + most_recent.name + ' most recently')

Problem is the looped query, it's nice and fast if you're a member of 1 club but if you join fifty my database crawls.

Edit: The answer from Nil does what I want but doesn't get the object. I don't really need the object but I do need another field as well as the datetime. If it's helpful the query:

Club.objects.annotate(last_election=Max('election__when'))

produces the raw SQL

SELECT "organisation_club"."id", "organisation_club"."name", MAX("organisation_election"."when") AS "last_election" 
    FROM "organisation_club" 
    LEFT OUTER JOIN "organisation_election" ON ( "organisation_club"."id" = "organisation_election"."club_id" ) 
    GROUP BY "organisation_club"."id", "organisation_club"."name"

I'd really like an ORM answer if at all possible (or a 'mostly' ORM answer).

Answer 1

I believe this is what you're looking for:

from django.db.models import Max, F

Election.objects.filter(club__members=me) \
                .annotate(max_date=Max('club__election_set__when')) \
                .filter(when=F('max_date')).select_related('elected')

Relations can be followed forwards and backwards again in a single statement, allowing you to annotate the max_date for any election related to the club of the current election. The F class allows you to filter a queryset based on selected fields in SQL, including any extra fields added through annotation, aggregation, joins etc.