4

I have turned the User model from django.contrib.auth.models into an API using Django Rest Framework. When I perform a User.objects.all() query in the API, I end up with hundreds of queries like this, every time I call the API:

SELECT `django_content_type`.`id`,
  `django_content_type`.`app_label`,
  `django_content_type`.`model`
FROM `django_content_type` 
WHERE `django_content_type`.`id` = 2

Hence the User API is quite slow.

Trying to optimise those out with prefetch_related I wound up with this query:

User.objects.all().prefetch_related('user_permissions__content_type__id')

But it produces the error:

'user_permissions__content_type__id' does not resolve to an item that supports 
prefetching - this is an invalid parameter to prefetch_related().

So how do I reduce the query count down from hundreds to the five or six I can usually optimise django rest framework down to?

For the record, here is my full code (abridged for relevance):

from rest_framework import viewsets
from django.contrib.auth.models import User

class UserViewSet(viewsets.ModelViewSet):
  model = User
  filter_fields = ('username',)

  def get_queryset(self):
    if self.request.user.is_staff:
        return User.objects.all().prefetch_related('user_permissions__content_type__id')

Note: this similar question is different because it's not referring to the built-in auth model. It's the built-in auth model that I am trying to use.

Community
  • 1
  • 1
John
  • 5,581
  • 6
  • 29
  • 46
  • Did you ever try removing `__id` at the end? I think that should work as-is. – Kevin Brown-Silva Jul 13 '15 at 17:41
  • possible duplicate of [Optimizing db queries in Django Rest Framework](http://stackoverflow.com/questions/26593312/optimizing-db-queries-in-django-rest-framework) – Kevin Brown-Silva Jul 13 '15 at 17:41
  • removing __id stops it crashing but it doesn't do the necessary prefetch. I still end up with hundreds of queries. It seems to me the built-in django auth user model simply can't be prefetched the way other models can be. – John Jul 22 '15 at 04:09
  • 1
    My question is different from Optimizing db queries in Django Rest Framework because the OP in that question uses a hand-rolled user model, whereas I am trying to use the built-in user model. It's the built-in model that I can't prefetch. My own hand-rolled models are easy to prefetch. – John Jul 22 '15 at 04:11

2 Answers2

2

You can use formfield_for_manytomany (https://docs.djangoproject.com/en/2.2/ref/contrib/admin/#django.contrib.admin.ModelAdmin.formfield_for_manytomany), query all permissions and select related content_type. 

from django.contrib import admin
from django.contrib.auth.models import User, Permission

@admin.register(User)
class UserAdmin(admin.ModelAdmin):
    def formfield_for_manytomany(self, db_field, request, **kwargs):
        if db_field.name == 'user_permissions':
            kwargs['queryset'] = Permission.objects.all().select_related('content_type')
        return super().formfield_for_manytomany(db_field, request, **kwargs)
kpotehin
  • 1,025
  • 12
  • 21
0

If you're using a custom user model, you'll need to setup your admin model by inheriting from UserAdmin. Otherwise, you'll have tons of queries that get generated and greatly slowdown your admin panel.

Here's an example:

from django.contrib.auth.admin import UserAdmin


@admin.register(models.AuthUser)
class AuthUserAdmin(UserAdmin):
    list_display = ['date_joined', 'username', 'email', 'is_staff', 'is_superuser']
    list_filter = ('is_staff', 'is_superuser')
    ordering = ['-username']
    search_fields = ['username', ]
    read_only_exclude = ('', )
JackieBoy
  • 1