Django 1.11 - nested use of OuterRef

I recently updated Django to version 1.11rc1 with a convex version due to the Subquery function introduced there.

Now let's say this is my use case: I have the following models - users, groups and permissions. So I have multiple users that I can group with (for example, the Administrators group) and Permissions, which are lists of users that can do some action (for example, I have User A, User B and Administrators who can create new users). Now I want to show all permissions with a number of users inside them. In other words, I want to create a QuerySet that will return all the Permissions information and calculate the number of users for each Permission. The first, obvious way to get around this would be to create a methodget_user_count

for a Permission model that will return all users from my ManyToMany relationship, but this would require at least 1 additional permission request, which is unacceptable for me since I plan on having a lot of permissions. This is where I want to use Subquery

.

So, to clarify things, 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 a queryset that will return all permissions with multiple users inside. For example, let's say I want to include users belonging to my groups, so I would 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 mine OuterRef

cannot be resolved as being used in "subquery filter 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 retrieve 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 am getting the error right on the first line:

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

      

The rest of the lines are irrelevant and do not affect the error. The strange thing is that the same syntax appears in the documentation: https://docs.djangoproject.com/en/dev/ref/models/expressions/#django.db.models.OuterRef

Question: what am I doing wrong? Or how do I achieve what I want in a different way (albeit efficiently)?

+3


source to share


1 answer


Well, this is a bug in Django: https://github.com/django/django/pull/9529

I fixed it by eliminating the depth of doulbe ( 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 this helps :)

+1


source







All Articles