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)?
source to share
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 :)
source to share