How to use Django prefetch_related

Question

One of my listing page has an related model for providing unicode function. I tried to prefetch to prevent duplicate queries.

Prefetch query is as below:

SELECT "pansys_modulex"."id", "pansys_modulex"."client", "pansys_modulex"."change_date", "pansys_modulex"."changed_by_id", "pansys_modulex"."create_date", "pansys_modulex"."created_by_id", "pansys_modulex"."language_id", "pansys_modulex"."short_text", "pansys_modulex"."module_id" FROM "pansys_modulex" WHERE ("pansys_modulex"."language_id" = '3' AND "pansys_modulex"."module_id" IN ('8', '9', '10', '12', '13', '14'))

Still, I get tens of duplicate queries like below:

SELECT "pansys_modulex"."id", "pansys_modulex"."client", "pansys_modulex"."change_date", "pansys_modulex"."changed_by_id", "pansys_modulex"."create_date", "pansys_modulex"."created_by_id", "pansys_modulex"."language_id", "pansys_modulex"."short_text", "pansys_modulex"."module_id" FROM "pansys_modulex" WHERE ("pansys_modulex"."language_id" = '3' AND "pansys_modulex"."module_id" = '8')

What is I missing here? I thought I would prefetch the query and django tries to use prefetch result if it can.

Edit: My Models and the prefetch command itself.

#models trimmed

class Application(GenericCommonModel):
    module = models.ForeignKey(Module, verbose_name=_('Module'))

class Module(GenericCommonModel):
    def __unicode__(self):
        short_text = None
        try:
            language = PanBasLanguage.objects.get(language=get_language())
            short_text = ModuleX.objects.get(module = self,language = language).short_text
        except ModuleX.DoesNotExist:
            print 'ModuleX > DoesNotExist'
        return short_text or self.abbreviation

class ModuleX(models.Model):
    module = custom_model_fields.PanNotNullForeignKey(Module, on_delete=models.CASCADE)
    language = custom_model_fields.PanNotNullForeignKey('panbas.PanBasLanguage')
    short_text = custom_model_fields.PanShortTextField()
    class Meta:
        verbose_name = _('Module Description')
        verbose_name_plural = _('Module Descriptions')
        unique_together = ('module', 'language')

class PanBasLanguage(GenericBasicModel):
    language_choices = settings.LANGUAGES
    language = custom_model_fields.PanNoneBlankCharField(choices=language_choices, max_length=8,
                                                         verbose_name=_('Language'), unique=True)


#in my view     

language = PanBasLanguage.objects.get(language=get_language())
x_result = ModuleX.objects.filter(language=language)
prefetch = Prefetch('module__modulex_set', queryset=x_result)
queryset = queryset.prefetch_related(prefetch)

in short, Language model has available languages, Module has ModuleX model to keep short_texts in different languages. And in my application listing I print out module in a column and this modules does modulex lookup seperately.

Answer 1

In short, Language model has available languages, Module has ModuleX model to keep short_texts in different languages. And in my application listing I print out module in a column and this modules does modulex lookup seperately.

So what you're looking for are module objects, and you want the short text in the given language.

Now, it's natural to approach this from the Module object and then you deal with the reverse foreign key. However, because the language limitation will always generate one result on the many side of the relation, it is better to approach this from the Many side, as you are now. As a bonus, you can use select_related as Escher points out.

Depending how complex the filtering on Module gets, you may want to use two queries (still eliminating queries inside the loop).

So, this is one way:

ModuleX.objects.filter(language__language=get_language(), **module_filters).select_related('module')

Where **module_filters is the filtering on module. You could do that with a little wrapper:

def _get_module_filters(self, **kwargs):
    filters = {}
    for k, v in **kwargs:
        filters['module__' + k] = v
    return filters

This saves some typing and allows you to keep focus on querying the module and not the language wrapper.

The two queries way is:

modules = Module.objects.filter(**module_filters)
translations = ModuleX.objects.filter(language__language=get_language(),
    module__in=modules).select_related('module')

Note that if at any point you call str() on the module instance, you are going to get additional queries again, since you do the same work there. This includes template code like:

{% for obj in translations %}
     {{ obj.module }}
{% endfor %}

Where translations is the above queryset.

Answer 2

From the docs:

select_related(*fields) Returns a QuerySet that will “follow” foreign-key relationships, selecting additional related-object data when it executes its query. This is a performance booster which results in a single more complex query but means later use of foreign-key relationships won’t require database queries.

and

prefetch_related, on the other hand, does a separate lookup for each relationship, and does the ‘joining’ in Python. This allows it to prefetch many-to-many and many-to-one objects, which cannot be done using select_related, in addition to the foreign key and one-to-one relationships that are supported by select_related.

Given yours is a foreign key relationship and not m2m, you should use select_related for that query.