Joining ManyToMany fields with prefetch_related in Django

Question

I may be missing something obvious, but I'm having trouble getting the connection in the ManyToMany field to work in the Django application. 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 dataset will look like this:

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

As you can see in this example, the My Role element is displayed twice, once for each area. I know that I can get a list of areas and then get a list of roles for each (which leads to N + 1 queries), but I would like to be effective if possible. So I found that prefetch_related might be what I wanted to use. However, when I use this, I get all Area values ​​as None . Here is 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)) 

Role names are suitable for proper driving, but there are no area names. What am I doing wrong here?

Answer 1

Unable to access r.areas__name for role r . You should still access roles through r.areas.all() . However, using prefetch_related , you select all related objects in one additional request instead of O (n) requests.

Since you want to order by area name, you should probably use the Area model for your set of queries, and then scroll 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 

This should give you an order if you want if the Role model is ordered by name by default. If not, you can use the Prefetch object to order the appropriate request.