Django similar queryset optimization-CodePudding

I am making a filmography website. However, I am having a hard time optimizing the queryset in the detailview.

class Actor(model.Models):
    id = models.IntegerField()
    name = models.CharField()

class Movie(model.Models):
    id = models.IntegerField()
    movie_title = models.CharField()
    actor = models.ManyToManyField(Actor, related_name='relations')

class ActorView(DetailView):
    model = Actor
    context_object_name = 'actor'
    template_name = 'actor.html'

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        context['movies'] = self.object.relations.all()
        return context


<div>
   {{ actor.name }}
   {% for movie in movies %}
       {{ movie.movie_title }}
       {% for actor_info in movie.actor.all %}
       {{ actor.name }}
       {% endfor %}
   {% endfor %}
</div>

I checked the sql tap in the django debug console.

   {% for actor_info in movie.actor.all %}
   {{ actor.name }}
   {% endfor %}

Similar queries are being executed repeatedly in the code above. And data connection and query execution time are also very slow. How can I optimize this part in the view or model?

CodePudding user response：

You can speed this up using prefetch_related

So in your view, change this line from

context['movies'] = self.object.relations.all()

context['movies'] = self.object.relations.all().prefetch_related("actor")

CodePudding user response：

In Django, select_related and prefetch_related are designed to stop the deluge of database queries that are caused by accessing related objects as mentioned

So for ForeignKey and OneToOneField relations you can use select_related and for ManyToManyField you can use prefetch_related which.

Django says

Prefetch related returns a QuerySet that will automatically retrieve, in a single batch, related objects for each of the specified lookups.

This has a similar purpose to select_related, in that both are designed to stop the deluge of database queries that is caused by accessing related objects, but the strategy is quite different.

select_related works by creating an SQL join and including the fields of the related object in the SELECT statement. For this reason, select_related gets the related objects in the same database query. However, to avoid the much larger result set that would result from joining across a ‘many’ relationship, select_related is limited to single-valued relationships - foreign key and one-to-one.


class ActorView(DetailView):
    model = Actor
    context_object_name = 'actor'
    template_name = 'actor.html'

    # Overide the get_object method and set prefetch_related
    def get_object(self, queryset):
        return super().get_object(queryset).prefetch_related("movies")

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        context['movies'] = self.get_object().relations.all()
        return context