Optimal django manytomany request

Question

I am having problems reducing the number of queries for a particular view. This is pretty heavy, 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() 

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

Given a player, how can I find out the last elected officer in each of the player clubs?

I currently 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') 

The problem is this looped request, it’s good and fast if you are in 1 club, but if you join fifty of my crawls in the database.

Edit: The response from Nil does what I want, but does not receive the object. I do not need an object, but I need another field, as well as time and date. If this is useful for a query:

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

creates 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 would really like the ORM answer, if at all possible (or the "mostly" ORM answer).

Answer 1

I believe this is what you are 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') 

Relationships can be repeated back and forth again in a single statement, allowing you to annotate max_date for any election related to the current election club. Class F allows you to filter a query based on selected fields in SQL, including any additional fields added by annotation, aggregation, joins, etc.

Answer 2

What you need is here in terms of SQL: query the Election table, group them by Club and save only the last selections of each club.

Now, how can we translate this into a Django ORM? By examining the documentation , we learn that we can do this with annotations. The trick is that you need to think the other way around. You want to annotate (add new data) each club with its latest selections. This gives us:

 Club.objects.annotate(last_election=Max('election__when')) # Use it in a for loop like that for club in Club.objects.annotate(last_election=Max('election__when')): print(club, club.last_election) 

Unfortunately, this only adds a date that does not answer your question! You need a name or full Club object. I searched and I still don't know how to do this. If all else fails, you can still use the raw SQL query in Django using the query, as in the first link.

Answer 3

The easiest way I can think of is to partially filter at the application level

If you do

 e = Election.objects.filter(club__members=me).select_related('elected') 

or

 e = me.club_set.election_set.select_related('elected') 

This is the only request, and he must return all the elections that have occurred for all the clubs in which the me member is located. Then you can use python to get the latest date. Of course, if you have a lot of choices in the club, you get much more data than you use.

Another way that should do this in two queries:

 # Get all member clubs & most recent election clubs = Club.objects.filter(members=me).annotate(last_election=Max('election__when')) # Create filters for election based on the club id and the latest election time election_Q = [Q(club__id=c.id) & Q(when=c.last_election) for c in clubs] # Combine filters with an OR election_filter = reduce(lambda f1, f2: f1 | f2, election_Q) # Get elections restricting by specific clubs & election date elections = Election.objects.filter(election_filter).select_related('elected') for e in elections: print '%s elected %s most recently at %s' % (e.club.name, e.elected, e.when) 

This is based on the @Nil method and uses its result to build the request in python, and then passes it to the second request. However, there is a limit on the size of the SQL statement, and if there are many clubs in the club, you may be in the limit. The limit is pretty high, although I only ever reached it when importing large datasets in a single INSERT statement, so I think this should be good for your purpose.

Sorry, I can't think of how Django ORM can link them together using a single SQL query. Django ORM is actually quite limited for complex queries, so if you really need performance, I think it's best to write the original SQL query.