Django prefetch_related and many to many to many relation

Question

I have these models:

class Permission(models.Model):

    name = models.CharField(u'Name', max_length=100)
    codename = models.CharField(u'Code name', max_length=100)

class Role(models.Model):

    name = models.CharField(u'Name', max_length=80)
    organization = models.ForeignKey(Organization)
    permissions = models.ManyToManyField(Permission, blank=True)

class UserProfileManager(models.Manager):

    def get_query_set(self):
        return super(UserProfileManager, self).get_query_set().select_related(
            'user', 'organization'
        )

class UserProfile(models.Model):

    user = models.OneToOneField(User)

    organization = models.ForeignKey(Organization, blank=True)
    roles = models.ManyToManyField(Role, blank=True)
    permissions = models.ManyToManyField(Permission, blank=True)

    objects = UserProfileManager()

I want to read all user permissions ('permissions' and 'roles__permissions') with maximum 2 SELECTS.

If I try to add prefetch_related('permissions', 'roles__permissions') to UserProfileManager I get 3 SELECTs (for permissions, roles and roles__permissions)

How to do it?

score 0 · Answer 1 · answered Dec 19 '12 at 16:55

These are some helper functions from me:

class UserProfile(models.Model):

    def get_permissions_user(self):
        if not hasattr(self, '_perm_user_cache'):
            perms = self.permissions.select_related()
            self._perm_user_cache = set([u'%s' % (p.codename) for p in perms])

        return self._perm_user_cache

    def get_permissions_group(self):
        if not hasattr(self, '_perm_group_cache'):
            perms = Permission.objects.filter(role__userprofile = self)
            self._perm_group_cache = set([u'%s' % (p.codename) for p in perms])

        return self._perm_group_cache

    def get_permissions(self):
        if not hasattr(self, '_perm_cache'):
            self._perm_cache = set()
            self._perm_cache.update(self.get_permissions_user())
            self._perm_cache.update(self.get_permissions_group())

        return self._perm_cache

score 0 · Answer 2 · edited May 23 '17 at 11:48

Read this question: A left outer reverse select_related in Django?

At that time, Django didn't have a prefetch_related(), so I solved it by querying the childmost model and regrouping the result, either by using the {% regroup %} template tag or in a view, using dicts.

In the case of a many-to-many, the childmost table is through:

# one way of optimizing: building a dict accessable by id
# beware to do this in really large tables
permissions = dict([(p.id, p) for p in Permission.objects.all()])

permission_roles = Role.permissions.through.select_related('role').all()
for permission_role in permission_roles:
    # permission_role is an object that associates a role with a permission
    # note that we haven't selected "permission", but permission_id is available:
    role = permission_role.role
    permission = permissions[permission_role.permission_id]

Django prefetch_related and many to many to many relation

2 Answers2