How to optimise number of queries when using raw_id_fields in Django Admin

Question

I have a data model as below:

class Candidate(models.Model):
    name = models.CharField()

class Skill(models.Model):
    name = models.CharField()

class CandidateSkill(models.Model):
    candidate = models.ForeignKey(Candidate)
    skill = models.ForeignKey(Skill, related_name='candidate_skills')
    proficiency = models.CharField()

And in the admin I have:

class CandidateSkillInline(admin.TabularInline):
    model = CandidateSkill
    fields = ('skill', )
    extra = 0
    raw_id_fields = ('skill',)

class CandidateAdmin(admin.ModelAdmin):
    model = Candidate
    fields = ('name',)
    inlines = [CandidateSkillInline]

Each candidate can have many skills. The problem here is that in the change page for each inline one query will be used to fetch the skill (SELECT ••• FROM "skill" WHERE "skill"."id" = <id>). If I add the field skill in CandidateSkillInline as read_only then there won't be extra queries. However I'd like to be able to add new items in the inlines. Thing I've tried:

1) Added custom formset to CandidateSkillInline:

class CandidateSkillInlineFormset(BaseInlineFormSet):
    def __init__(self, *args, **kwargs):
        super(CandidateSkillInlineFormset, self).__init__(*args, **kwargs)
        self.queryset = self.queryset.select_related('skill')

2) Override the get_queryset on the inline:

def get_queryset(self, request):
    super(CandidateSkillInline, self).get_queryset(request).select_related('skill')

3) Override the get_queryset on CandidateAdmin:

def get_queryset(self, request):
    return super(CandidateAdmin, self).get_queryset(request).prefetch_related('candidate_skills__skill')

However, still I get a query for each skill. The only way the queries are not sent is when I set the skill in read_only_fields in CandidateSkillInilne. The question is how can I select or prefetch the skills in one query rather than one for each inline?

Answer 1

Well, the design of the ForeignKeyRawIdWidget is not so elegant. Instead of just show some data in a specific way, which is the basic responsibility of a widget, the ForeignKeyRawIdWidget make an extra query to display more pertinent information in the screen (it shows the value of a str(obj) method).

The query is executed in the label_and_url_for_value method. So, you can try to use your own custom widget to avoid this query, but you will have to think about the tradeoffs in the visualization.

One possible solution:

from django.contrib.admin.widgets import ForeignKeyRawIdWidget
from django.urls import reverse

class OptimisedForeignKeyRawIdWidget(ForeignKeyRawIdWidget):
    def label_and_url_for_value(self, value):
        try:
            url = reverse(
                '%s:%s_%s_change' % (
                    self.admin_site.name,
                    self.rel.model._meta.app_label,
                    self.rel.model._meta.object_name.lower(),
                ),
                args=(value,)
            )
        except NoReverseMatch:
            url = ''  # Admin not registered for target model.
        return str(value), url

The last step, you will have to set the custom widget in the ModelForm class. And there are many ways to do that.

Answer 2

This seems like you are trying to implement your own ManyToManyField. Can you use the ManyToManyField and inline instead? It has a nice multiple-select widget in the admin.

https://docs.djangoproject.com/en/dev/ref/contrib/admin/#working-with-many-to-many-models