Оптимизация запросов с ForeignKey на self модель

Есть следующие модели

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)
    ...

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__"

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

После оптимизации запросов - я вижу подобные запросы

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

Запросов, подобных на второй - очень много. Так понимаю, что причина в рекурсивных связях?

Как можно оптимизировать эти запросы через Django ORM?

Вернуться на верх