Django 1.11 - nested OuterRef usage

Question

I recently updated Django to the bleeding-edge version 1.11rc1 because of the Subquery feature that was introduced there.

Now, let's say this is my use case: I have following models - Users, Groups and Permissions. So, I have some Users whom I can group (e.g. Administrators group) and Permissions - which are lists of users that can do some things (e.g. I have User A, User B and Administrators who can create new users). What I want to do now is display all of the Permissions with a number of users inside them efficiently. So in other words, I want to make a QuerySet which would return all the information about the Permissions and calculate the number of the users for each Permission. The first, obvious way to work-around this would be to create a get_user_count method for the Permission model which would return all users from my ManyToMany relationships, but that would require at least 1 additional query per Permission, which is unacceptable for me, as I'm planning to have a lot of Permissions. This is where I want to use Subquery.

So, to clarify things up - this is models.py:

class User(models.Model):
    name = models.CharField(max_length=20)

class Group(models.Model):
    users = models.ManyToManyField(User)

class Permission(models.Model):
    users = models.ManyToManyField(User)
    groups = models.ManyToManyField(Group)

And I want to create queryset that will return all Permissions with a number of users inside. For the sake of example, let's say I only want to include Users that belong to my groups - so I'd have something like this:

groups = Group.objects.filter(permission=OuterRef('pk'))
users = User.objects.filter(group__in=groups)
queryset = Permission.objects.annotate(
    user_no=Subquery(users.annotate(c=Count('*')).values('c'))
)

The problem here is that my OuterRef cannot be resolved as used in "subquery's filter's filter":

This queryset contains a reference to an outer query and may only be used in a subquery.

Although, when I use another subquery to fetch the groups:

groups = Group.objects.filter(permission=OuterRef(OuterRef('pk')))
users = User.objects.filter(group__in=Subquery(groups))
queryset = Permission.objects.annotate(
    user_no=Subquery(users.annotate(c=Count('*')).values('c'))
)

I get an error right in the first line:

int() argument must be a string, a bytes-like object or a number, not 'OuterRef'

The rest of the lines do not matter and have no influence on the error. The weird thing is, the exact same syntax appears in the documentation: https://docs.djangoproject.com/en/dev/ref/models/expressions/#django.db.models.OuterRef

The question is: what do I do incorrectly? Or how to achieve what I want in other way (although efficiently)?

Inpour

I have the same problem. Have you found a solution? – Amimia

@TomPalmer unfortunately no. Stable version of Django 1.11 has been out for some time already, so it seems it wasn't RC version problem. – Inpour

Thanks for the info. My workaround so far is a RawSQL expression. I'm also considering making views and mapping unmanaged models to those. – Amimia

Answer 1

Well, it's a bug in Django: https://github.com/django/django/pull/9529

I fixed it by excluding double-depth (OuterRef(OuterRef('pk'))) using annotations:

return self.annotate(category=Subquery(
    # this is the "inner" subquery which is now just an annotated variable `category`
    Category.objects.filter(offer=OuterRef('pk'))[:1].values('pk')
)).annotate(fs=Subquery(
    # this is the "outer" subquery; instead of using subquery, I just use annotated `category` variable
    Category.objects.filter(pk=OuterRef('category')).values('slug')
))

Hope it helps :)

Edieedification