'Optimization queries with foreign key to self model

I have following models.py

class Comment(models.Model):
    parent = models.ForeignKey("self", on_delete=models.CASCADE, null=True, blank=True, related_name="replys")
    ...


class Notification(models.Model):
    comment = models.ForeignKey(Comment, null=True, on_delete=models.CASCADE)
    ...

and serializers.py

class CommentSerializer(serializers.ModelSerializer):
    replys = serializers.SerializerMethodField()
    ...

    def get_replys(self, obj):
        queryset = obj.replys.select_related("author", "author__avatar").prefetch_related("author__engagement")
        serializer = CommentSerializer(queryset, many=True, context=self.context)
        return serializer.data


class NotificationSerializer(serializers.ModelSerializer):
    comment = CommentSerializer(read_only=True)
    ...

    class Meta:
        model = models.Notification
        fields = "__all__"

and service.py

from django.contrib.auth import get_user_model


def send_notifs(recipient):
    from vitalvoices.vital_notif.serializers import NotificationSerializer
    latest_notifications = Notification.objects.select_related(
        "comment",
        "comment__author",
        "comment__author__avatar",
        "comment__topic",
        "comment__parent",
    ).prefetch_related(
        "comment__author__engagement",
        "comment__reports",
        "comment__parent__replys",
    ).filter(recipient=recipient, checked=False).order_by("-pk")[:20]
    notifications_data = NotificationSerializer(latest_notifications, many=True).data

After queries optimization I have this log

SQL - SELECT "discussions_comment"."id", "discussions_comment"."author_id", "discussions_comment"."topic_id", "discussions_comment"."parent_id", "discussions_comment"."body", "discussions_comment"."created_at" FROM "discussions_comment" WHERE "discussions_comment"."parent_id" IN (82)
Time - 0.001
SQL - SELECT "discussions_comment"."id", "discussions_comment"."author_id", "discussions_comment"."topic_id", "discussions_comment"."parent_id", "discussions_comment"."body", "discussions_comment"."created_at", "users_user"."id", "users_user"."password", "users_user"."last_login", "users_user"."is_superuser", ...  FROM "discussions_comment" LEFT OUTER JOIN "users_user" ON ("discussions_comment"."author_id" = "users_user"."id") LEFT OUTER JOIN "core_mediafile" ON ("users_user"."avatar_id" = "core_mediafile"."id") WHERE "discussions_comment"."parent_id" = 154
Time - 0.031

I have many queries such as second query. Maybe this problem linked with recursive relation (ForeignKey to self model).

How can to optimize these queries using Django ORM?

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'Optimization queries with foreign key to self model

Sources

Related Questions