[Answer]-Django prefetch_related causing extra queries in M2M relationship

I was having this same problem: I was executing additional queries each time I tried to reference something I thought I’d pulled back in my query! I think I found the solution. In order to prevent additional lookups for each instance, you can extend your prefetch_related to include the lookup tables you need. Indeed, this produces one more query, but it doesn’t produce 10,000 queries, assuming you have 10,000 persons you are trying to access.

    students = Person.objects.select_related('gender',
                                             'old_race',
                                             'ethnicity',
                                             'father_education_level',
                                             'mother_education_level',
                                             ).prefetch_related('personethnicity_set',
                                                                'personethnicity_set__ethnicity',
                                                                'studentsemester_set',
                                                                'studentsemester_set__semester',
                                                                'studentsemester_set__first_mode_admission',
                                                                'studentsemester_set__classification',
                                                                'studentsemester_set__academic_status',
                                                                'studentsemester_set__studentsemestermajor_set',
                                                                'studentsemester_set__studentsemestermajor_set__major_type',
                                                                'studentsemester_set__studentsemestermajor_set__major',
                                                                'studentsemester_set__studentsemestermajor_set__major__registrar_school').filter(ftic_cohort=oursem).order_by('-studentsemester__semester__ccyys')

In the above code, I am able to get all the lookup tables (one-to-ones) for my person records using the select_related. Then, I am able to get my many-to-manys using the prefetch_related (of personethnicity and studentsemester), and I am able to get the lookup tables that go with those many-to-many tables by doing personethnicity__ethnicity, etc.

In a query that pulls back 8000 students or so, I am only doing 15 SQL queries with this code. I then reference my prefetched fields by doing (in my case, within a loop for stud in students) like this:

studict['ethnicities'] = ''
for myeth in stud.personethnicity_set.all(): 
    studict['ethnicities'] += str(myeth.ethnicity) + ' '

Hope this helps.

Something else to be aware of is if there is an order by on the query when accessing it in the template. For example doing:
person.questions.all.first
puts an ascending sort on the ID, so in your prefetch you would need to use the Prefetch object to designate a sort order that is the same, like so:

persons = Person.objects.filter(name="Foo").prefetch_related(Prefetch("questions", queryset=Question.objects.all().order_by("id")))