select_related doesn't work(I can't get rid of unnecessary SQL queries).

[SILVERHAND]

I’m trying to get rid of unnecessary SQL queries using select_related and prefetch_related, but for some reason they only helped me get rid of two unnecessary queries. I thought maybe I could change the SQL query, but that didn’t help either.

My class

class GameDetail(LoginRequiredMixin, DetailView):
    model = Game
    context_object_name = 'game'
    template_name = 'main/game_detail.html'

    def get_queryset(self):
        return Game.objects.prefetch_related(
            Prefetch('comments', queryset=Comment.objects.select_related('author'))
        )

    def post(self, request, **kwargs):
        comment_form = CommentForm(request.POST)

        if comment_form.is_valid():
            user_comment = comment_form.save(commit=False)
            slug = self.kwargs['slug']
            game = self.get_object()
            user_comment.game = game
            user_comment.author = request.user
            user_comment.save()

            return redirect('game_detail', slug=slug)

        if 'delete_comment' in request.POST:
            if request.user.is_authenticated:

                slug = self.kwargs['slug']
                comment_id = request.POST.get('comment_id')
                comment = get_object_or_404(Comment, pk=comment_id)
                if comment.author == request.user:
                    comment.delete()
                    return redirect('game_detail', slug=slug)
            return self.render_to_response(
                self.get_context_data(form=comment_form))

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        comments = Comment.objects.filter(game=self.object).select_related('author')
        for comment in comments:
            comment.str_value = comment.text
        context['comments'] = comments
        context['game'] = self.object
        context['form'] = CommentForm
        return context

My model

class Comment(MPTTModel):
    game = models.ForeignKey(
        Game, on_delete=models.CASCADE,
        related_name='comments',
    )
    parent = TreeForeignKey(
        'self',
        on_delete=models.CASCADE,
        null=True,
        blank=True,
        related_name='children',
        db_index=True,
    )
    author = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name='comments_author',
        db_index=True
    )
    text = models.TextField()
    time_add = models.DateTimeField(auto_now_add=True)
    status = models.BooleanField(default=True)

    class MPTTMeta:
        ordering_insertion_by = ['status']

    def __str__(self):
        return f'{self.author}:{self.text}'

[KenWhitesell]

Welcome @SILVERHAND !

Where are you seeing “unnecessary queries”? (What are you looking at that is giving you this information?)

[SILVERHAND]

I mean similar and duplicates SQL queries. This information is located in the Django Debug Toolbar

[KenWhitesell]

Ok, we need to see those queries, and all the details you have available to you from DDT.

[SILVERHAND]

All of these duplicate SQL quires have the same details(Also, I can only send 1 screenshot at a time).

[SILVERHAND]

[SILVERHAND]

[SILVERHAND]

[SILVERHAND]

[SILVERHAND]

[KenWhitesell]

This does look like these queries are being caused by your template rendering __str__ - is this in a select field?

First, unless you can demonstrate that these repeated queries are causing a problem, I probably wouldn’t worry about it. I would suspect that Django’s internal cache is “good enough” for most practical purposes.

However, if it isn’t, you might be able to resolve this a couple different ways. (What I don’t know is how Django manages its references to objects while rendering.)

If Django is referencing the same instance of these models, you could use the instance as a kind of cache for the __str__ method. (Check to see if the resolved value exists in the object, if so, use it, if not, create it and save it.)

If those aren’t the same instances being referenced, then you could use the regular Django cache to store them.

Or, if they are part of a select list, you could cache the entire list and use that as needed.

Or, you could possibly render these selections as a datalist, and then reference this list from multiple select elements.

(I’m not sure what’s going to work best in your situation. I’d need to understand a lot more about the page you’re creating and the templates being rendered to be able to make a specific suggestion. Hopefully one or more of these ideas may help.)

In any event, the first step is to identify whether this is really a problem or not.

[SILVERHAND]

@KenWhitesell

This does look like these queries are being caused by your template rendering __str__ - is this in a select field?

I removed that __str__ and it worked. But I don’t understand why it worked? It applies to the admin panel, what does it have to do with displaying data on the HTML page?

[KenWhitesell]

@SILVERHAND

It applies to the admin panel, what does it have to do with displaying data on the HTML page?

That method is not only used by the admin. It’s used by anything that needs to create a string representation of that object, which includes rendering it as the options in a select field.