What's the Difference Between Select_Related and Prefetch_Related in Django Orm

What's the difference between select_related and prefetch_related in Django ORM?

Your understanding is mostly correct. You use select_related when the object that you're going to be selecting is a single object, so OneToOneField or a ForeignKey. You use prefetch_related when you're going to get a "set" of things, so ManyToManyFields as you stated or reverse ForeignKeys. Just to clarify what I mean by "reverse ForeignKeys" here's an example:

class ModelA(models.Model):
    pass

class ModelB(models.Model):
    a = ForeignKey(ModelA)

ModelB.objects.select_related('a').all() # Forward ForeignKey relationship
ModelA.objects.prefetch_related('modelb_set').all() # Reverse ForeignKey relationship

The difference is that select_related does an SQL join and therefore gets the results back as part of the table from the SQL server. prefetch_related on the other hand executes another query and therefore reduces the redundant columns in the original object (ModelA in the above example). You may use prefetch_related for anything that you can use select_related for.

The tradeoffs are that prefetch_related has to create and send a list of IDs to select back to the server, this can take a while. I'm not sure if there's a nice way of doing this in a transaction, but my understanding is that Django always just sends a list and says SELECT ... WHERE pk IN (...,...,...) basically. In this case if the prefetched data is sparse (let's say U.S. State objects linked to people's addresses) this can be very good, however if it's closer to one-to-one, this can waste a lot of communications. If in doubt, try both and see which performs better.

Everything discussed above is basically about the communications with the database. On the Python side however prefetch_related has the extra benefit that a single object is used to represent each object in the database. With select_related duplicate objects will be created in Python for each "parent" object. Since objects in Python have a decent bit of memory overhead this can also be a consideration.

Django proper use of select_related or prefetch_related on a ForeignKey

That is exactly what prefetch_related does.

Product.objects.prefetch_related('product_images')

However, there's no point in using query.sql_with_params() to diagnose this: prefetch_related does two queries, and the second one won't show up there. You should use django.db.connection.queries to examine the queries that Django is making, or even better use the Django debug toolbar to show you.

Django prefetch and select related

In your specific case I suppose better to use annotation instead of prefetch:

from django.db.models import Count, Q

cities = City.objects
.annotate(bank_count=Count("street", filter=Q(street__building_id=1)))
.annotate(cinema_count=Count("street", filter=Q(street__building_id=2)))
.annotate(church_count=Count("street", filter=Q(street__building_id=3)))

Now you can directly use bank_count, cinema_count and church_count attributes:

for city in cities: 
   print(city.bank_count)
   print(city.cinema_count)
   print(city.church_count)

In case you want to use prefetch_related you need to use Prefetch object. This allows you tof filter prefetched objects:

City.objects.prefect_related(
    Prefetch("street_set", queryset=Street.objects.filter(building_id=1), to_attr='has_bank'),
    Prefetch("street_set", queryset=Street.objects.filter(building_id=2), to_attr='has_cinema'),
    Prefetch("street_set", queryset=Street.objects.filter(building_id=3), to_attr='has_church')
)

Note to_attr argument this helps you to prefetch same model's objects with different filters to different attributes. So you can do now:

for city in cities: 
   print(city.has_bank)
   print(city.has_cinema)
   print(city.has_church)

Does it matter in which order you use prefetch_related and filter in Django?

It doesn't matter where you specify prefetch_related as long as it's before any records are fetched. Personally I put things like prefetch_related, select_related. and only at the end of the chain but that just feels more expressive to me from a code readability perspective.

But that is not true of all manager methods. Some methods do have different effects depending on their position in the chain, for example order_by can have positional significance when used with distinct (group by).

django model select_related or prefetch_related child model

To customize how a queryset is fetched in a generic view one needs to override the get_queryset method of the view. Also if one wants to use select_related on a prefetched object one should use Prefetch objects [Django docs] and specify their queryset. Since this is a DetailView and hence deals with one object you will need to override get_object instead:

from django.db.models import Prefetch
from django.http import Http404
from django.utils.translation import gettext as _

class ReceipeDetailView(PermissionRequiredMixin, DetailView):
    permission_required = 'item_management.view_receipemaster'
    model = ReceipeMaster
    template_name = 'receipes/show.html'
    context_object_name = 'receipe'
    
    def get_object(self, queryset=None):
        # Use a custom queryset if provided; this is required for subclasses
        # like DateDetailView
        if queryset is None:
            queryset = self.get_queryset()

        # Next, try looking up by primary key.
        pk = self.kwargs.get(self.pk_url_kwarg)
        slug = self.kwargs.get(self.slug_url_kwarg)
        if pk is not None:
            queryset = queryset.filter(pk=pk)

        # Next, try looking up by slug.
        if slug is not None and (pk is None or self.query_pk_and_slug):
            slug_field = self.get_slug_field()
            queryset = queryset.filter(**{slug_field: slug})

        # If none of those are defined, it's an error.
        if pk is None and slug is None:
            raise AttributeError(
                "Generic detail view %s must be called with either an object "
                "pk or a slug in the URLconf." % self.__class__.__name__
            )
        queryset = queryset.select_related('item').prefetch_related(
            Prefetch(
                'items',
                 queryset=ReceipeDetail.objects.select_related('item_type', 'item')
            )
        )
        try:
            # Get the single item from the filtered queryset
            obj = queryset.get()
        except queryset.model.DoesNotExist:
            raise Http404(_("No %(verbose_name)s found matching the query") %
                          {'verbose_name': queryset.model._meta.verbose_name})
        return obj

select_related and prefetch_related django

• use select_related(fieldname) to load objects referenced directly from this model by a foreign key (many2one)
• use prefetch_related(fieldname) to load objects referenced indirectly (object that have references to this model)
  
  • by one2many (that means, this object is referenced from another model by some foreign key)
  • by many2many (that's your case - both Student and Subject are referenced from an intermediate technical model)

qs = Student.objects.select_related(
    'student', 'section', 'profile',
).prefetch_related(
    'enrolled_subjects',
)

---

How to add filters on a related field - just use filter as usual, but prefix names of fields on a related model with a name of referrer field.

Example:

Let's say, we need to get such Students, that they have such Sections, that their section is 'example'.

How do we do that in python:

Student.objects.filter(section__section='example')
#                 (1)->^^^^^^^  ^^^^^^^<-(2)

1. the name of field on the Student model
2. the name of field on the Section model

You're free combine prefetches, selects and filters in any order.

Student.objects.filter(...).prefetch_related(...).select_related(...).filter(...)

Django prefetch_related and select_related

From the FineManual(tm) (emphasis is mine):

There may be some situations where you wish to call select_related()
with a lot of related objects, or where you don’t know all of the
relations. In these cases it is possible to call select_related() with
no arguments. This will follow all non-null foreign keys it can find -
nullable foreign keys must be specified. This is not recommended in
most cases as it is likely to make the underlying query more complex,
and return more data, than is actually needed.

---

Related Topics