-1

I need to fetch a list of users and a list of permissions (guardian object permissions) each user has. The problem is that every way I am trying to do this runs into the N+1 problem. Whether I simply loop over the user list and get permissions from the set

for user in User.objects.all():
    data = UserSerializer(user).data
    data['permissions'] = PermissionSerializer(user.userobjectpermission_set, many=True)

Or if I use prefetch_related()

for user in User.objects.prefetch_related('userobjectpermission_set'):
    data = UserAndPermSerializer(user).data

It ends up fetching the user list first and then running a separate permission query for every single user.

I can write a raw SQL statement and serialize it myself, but I would rather use the serializers I already have and so I would like to have the users and permissions as model instances. Any way to accomplish what I need using the ORM?

Django is version 1.11 LTS and I cannot upgrade to 2.x any time soon.

Mad Wombat
  • 14,490
  • 14
  • 73
  • 109
  • The whole point of prefetch_related is to fetch all related objects in one query. Are you sure it doesn't work? – Frax Nov 03 '18 at 01:16
  • I haven't checked the actual database statement logs yet, but it takes almost exactly the same time on my database as the manual looping, so I am reasonably sure that is what it does. – Mad Wombat Nov 03 '18 at 20:01
  • Do you use Direct Foreign Key available in django-guardian? – Adam Dobrawy Nov 04 '18 at 00:34
  • I am, not sure what you are talking about. I am using the standard user model and the user permission model from guardian. Nothing else is involved here. – Mad Wombat Nov 04 '18 at 02:22
  • Ah, found it in the docs. No, I am not using these, but I am not sure how these would help. I need to fetch all permissions a user has, not check if she has permissions for a specific object. – Mad Wombat Nov 04 '18 at 02:26
  • @Frax you seem to be mistaken about at least one thing (the docs say that you are) and yet you are trigger happy on the downvotes. You are whats wrong with SO. I have about 150 users on my dev system, my database is postgres hosted on RDS and both the loop and the prefetch_related() take some 25 seconds each. – Mad Wombat Nov 05 '18 at 04:31
  • 1
    I don't think I'm mistaken, but I'll be happy to admit that, if you show me where I'm wrong. The point is, perhaps there is a query on each object, but you didn't dig to find what is this query, and came with a pretty vague speculation instead, suggesting a bug in Django. That could be ok-ish if your question contained MCVE, but it doesn't. I downvoted on the base of question not showing enough work, and I'll be happy to turn it into upvote once you bring something substantial. It's simply a push to do a bit more debugging yourself. – Frax Nov 05 '18 at 08:02
  • 25 seconds is a lot of time, even for 150 queries. There is likely another underlying issue. Maybe slow connection, maybe slow RDS instance, maybe just a lot of permission objects? How long does it take to execute `list(User.objects.all()); list(Permission.objects.all())`? – Frax Nov 05 '18 at 08:22
  • The list of users and regular permissions takes about 12ms, since it only runs 2 regular select queries. But this doesn't tell me anything either, since I am not using the regular permissions, I am using object level permissions provided by guardian. `list(User.objects.all()); list(UserObjectPermission.objects.all())` takes about 90ms, also 2 queries, but the result set of the second query is longer. – Mad Wombat Nov 05 '18 at 15:48

1 Answers1

0

If you're prioritizing database reads over things like readability, maintainability, and developer efficiency then you can do this in two reads:

  • read all users: User objects.all()
  • read all permissions: Permission.objects.all()

Then stitch the permissions to the user on the application level.

This sucks, but it's efficient on database reads

Rich Tier
  • 9,021
  • 10
  • 48
  • 71