Joining ManyToMany fields with prefetch_related in Django

Question

I may be missing something obvious, but I'm having trouble getting a join on a ManyToMany field to work in a Django app. I have two models:

class Area(models.Model):
    name = CharField(...)

class Role(models.Model):
    name = CharField(...)
    areas = ManyToManyField('Area', ...)

My goal is to have the equivalent of this query:

select a.name, r.name
from area a
join area_role ar on ar.area_id = a.id
join role r on ar.role_id = r.id
order by a.name, r.name

The resulting data set would look like:

Area    Role
---------------------
A       My Role
A       Your Role
A       Yo Mamas Role
B       My Role
B       Some Other Role

As you can see in the example, the My Role item appears twice, once for each area. I know I could get the list of areas and then get a list of roles for each (resulting in N+1 queries), but I'd like to be efficient about it if possible. As such, I found that prefetch_related might be what I wanted to use. When I use this, however, I end up with all Area values as None. Here's what I tried:

rqs = ( Role.objects.filter(areas__id__in=[1,2,3])
        .prefetch_related(areas).order_by('areas__name', 'name') )

for r in rqs:
    print("Area Name: " + str(r.areas.name))
    print("Role Name: " + str(r.name))

The role names come along for the ride correctly, but the area names do not. What am I doing wrong here?

Answer 1

It isn't possible to access r.areas__name for a Role r. You still have to access the roles via r.areas.all(). However, by using prefetch_related, you fetch all the related objects in one extra query, instead of O(n) queries.

Since you want to order by area name, you should probably use the Area model for your queryset, then loop through the related roles.

areas = Area.objects.filter(id__in=[1, 2, 3]).order_by('name').prefetch_related('role_set')

for area in areas:
    roles = area.role_set.all()
    for role in roles:
        print area.name, roles.name

That should give you the ordering you want, as long as the Role model is ordered by name by default. If not, you could use a Prefetch object to order the related queryset.