django duplicate SQL queries stemming from model method get_absolute_url that accesses ForeignKey relationship

Question

Problem Summary

I have a class-based list view that just displays links for each object along with some data from some of the object fields. The href for the links is generated using a get_absolute_url method that I've written in the model itself. The problem is that the database is queried each time get_absolute_url is run. This causes many duplicate queries (in the future, with more objects, this will be a problem).

Attempted Solutions

My get_absolute_url method accesses some ForeignKey fields from my model, so I tried to use .select_related() for my queryset in my view. This did not change anything though.

Question

How can I eliminate the duplicate queries from running get_absolute_url?

Code

models.py

class LanguageLocale(models.Model):
    """model for representing language locale combinations"""

    class LANG_CODES(models.TextChoices):
        EN = 'en', _('English')
        ES = 'es', _('español')
        QC = 'qc', _("K'iche'")

    lang = models.CharField(max_length=2, choices=LANG_CODES.choices, blank=False)

class Scenario(models.Model):
    """model for representing interpreting practice scenarios"""

    scenario_id = models.CharField(max_length=20, primary_key=True)

    lang_power = models.ForeignKey(LanguageLocale)
    lang_primary_non_power = models.ForeignKey(LanguageLocale)

    class SCENARIO_STATUSES(models.TextChoices):
        PROD = 'PROD', _('Production')
        STGE = 'STGE', _('Staged')
        EXPR = 'EXPR', _('Experimental')

    status = models.CharField(
        max_length=4, choices=SCENARIO_STATUSES.choices, default='EXPR')

    def get_absolute_url(self):
        """Returns the URL to access a detail record for this scenario."""
        return reverse('dialogue-detail', kwargs={
            'lang_power': self.lang_power.lang,
            'lang_primary_non_power': self.lang_primary_non_power.lang,
            'pk': self.scenario_id
            }
        )

views.py

class ScenarioListView(generic.ListView):
    """View class for list of Scenarios"""

    queryset = Scenario.objects.select_related(
        'domain_subdomain', 'lang_power', 'lang_primary_non_power'
    )
    
    demo = Scenario.objects.get(scenario_id='demo')
    prod = Scenario.objects.filter(status='PROD')
    staged = Scenario.objects.filter(status='STGE')
    experimental = Scenario.objects.filter(status='EXPR')
    
    extra_context = {
        'demo': demo,
        'prod': prod,
        'staged': staged,
        'experimental': experimental,
    }

scenario_list.html

{% extends "home.html" %}

{% block content %}
  <h1>Practice Dialogues</h1>
  <section>
    {% if prod %}
      <ul>
        {% for scenario in prod %}
          <li>
            <a href="{{ scenario.get_absolute_url }}">{{  scenario.title }}</a> ({{ 
            scenario.domain_subdomain.domain }})
          </li>
        {% endfor %}
      </ul>
    {% else %}
      <p>There are no practice scenarios. Something went wrong.</p>
    {% endif %}
  </section>
  {% if user.is_staff %}
    <section>
      {% if staged %}
        <article>
          <h2>Staged Dialogues</h2>
          <ul>
            {% for scenario in staged %}
              <li>
                <a href="{{ scenario.get_absolute_url }}">{{  scenario.title }}</a> ({{ 
                scenario.domain_subdomain.domain }})
              </li>
            {% endfor %}
          </ul>
        </article>
      {% endif %}
      {% if experimental %}
        <article>
          <h2>Experimental Dialogues</h2>
          <ul>
            {% for scenario in experimental %}
              <li>
                <a href="{{ scenario.get_absolute_url }}">{{  scenario.title }}</a> ({{ 
                scenario.domain_subdomain.domain }})
              </li>
            {% endfor %}
          </ul>
        </article>
      {% endif %}
    </section>
  {% endif %}
{% endblock %}

duplicate queries readout (from django debug toolbar)

This query is duplicated 3 times:

SELECT "scenario_languagelocale"."id",
       "scenario_languagelocale"."lang_locale",
       "scenario_languagelocale"."lang"
  FROM "scenario_languagelocale"
 WHERE "scenario_languagelocale"."id" = 1
 LIMIT 21 6 similar queries.  Duplicated 3 times.

This query is duplicated 2 times:

SELECT "scenario_languagelocale"."id",
       "scenario_languagelocale"."lang_locale",
       "scenario_languagelocale"."lang"
  FROM "scenario_languagelocale"
 WHERE "scenario_languagelocale"."id" = 3
 LIMIT 21 6 similar queries.  Duplicated 2 times.

Answer 1

I would advise not to use extra_context. This will prevent reevaluating the query between two requests, which means that if you thus add an extra Scenario, and you do not restart the server, it will not "update' the list when you ask for the scenario's again. The demo query is even more problematic, since that runs immediately when you start the server, and thus can raise errors if you run this on a server with a database, without such demo Scenario.

You can however work with queryset which already performs a .select_related(…) on the necessary relations here, so:

from django.shortcuts import get_object_or_404


class ScenarioListView(generic.ListView):
    """View class for list of Scenarios"""
    queryset = Scenario.objects.select_related(
        'domain_subdomain', 'lang_power', 'lang_primary_non_power'
    )

    def get_context_data(self, *args, **kwargs):
        return super().get_context_data(
            *args,
            **kwargs,
            demo=get_object_or_404(self.queryset, scenario_id='demo'),
            prod=self.queryset.filter(status='PROD'),
            staged=self.queryset.filter(status='STGE'),
            experimental=self.queryset.filter(status='EXPR'),
        )