Django: Как переписать необработанный SQL в queryset

Необработанный SQL был переписан, поскольку время выдачи следующего набора запросов было очень медленным.

# queryset
Video.objects.annotate(
    is_viewed=Exists(History.objects.filter(user=user, video=OuterRef("pk"))),
    is_favorited=Exists(
        Favorite.objects.filter(user=user, video=OuterRef("pk"))
    ),
    is_wl=Exists(
        Track.objects.filter(
            playlist__user=user, playlist__is_wl=True, video=OuterRef("pk")
        )
    ),
).filter(
    Q(title__contains=value) | Q(tags__name__contains=value),
    is_public=True,
    published_at__lte=timezone.now(),
).order_by("-published_at").distinct()[:20]

SELECT DISTINCT "videos_video"."id",
                "videos_video"."title",
                "videos_video"."thumbnail_url",
                "videos_video"."preview_url",
                "videos_video"."embed_url",
                "videos_video"."duration",
                "videos_video"."views",
                "videos_video"."is_public",
                "videos_video"."published_at",
                "videos_video"."created_at",
                "videos_video"."updated_at",
                EXISTS
  (SELECT (1) AS "a"
   FROM "videos_history" U0
   WHERE (U0."user_id" IS NULL
          AND U0."video_id" = "videos_video"."id")
   LIMIT 1) AS "is_viewed",
                EXISTS
  (SELECT (1) AS "a"
   FROM "videos_favorite" U0
   WHERE (U0."user_id" IS NULL
          AND U0."video_id" = "videos_video"."id")
   LIMIT 1) AS "is_favorited",
                EXISTS
  (SELECT (1) AS "a"
   FROM "videos_track" U0
   INNER JOIN "videos_playlist" U1 ON (U0."playlist_id" = U1."id")
   WHERE (U1."is_wl"
          AND U1."user_id" IS NULL
          AND U0."video_id" = "videos_video"."id")
   LIMIT 1) AS "is_wl"
FROM "videos_video"
LEFT OUTER JOIN "videos_video_tags" ON ("videos_video"."id" = "videos_video_tags"."video_id")
LEFT OUTER JOIN "videos_tag" ON ("videos_video_tags"."tag_id" = "videos_tag"."id")
WHERE ("videos_video"."is_public"
       AND "videos_video"."published_at" <= '2022-01-03 05:20:16.725884+00:00'
       AND ("videos_video"."title" LIKE '%word%'
            OR "videos_tag"."name" LIKE '%word%'))
ORDER BY "videos_video"."published_at" DESC
LIMIT 20;

Я ускорил вышеприведенный запрос, чтобы он выглядел так, как показано ниже. Но как я могу воспроизвести это в наборе запросов Django?

SELECT DISTINCT "t"."id",
                "t"."title",
                "t"."thumbnail_url",
                "t"."preview_url",
                "t"."embed_url",
                "t"."duration",
                "t"."views",
                "t"."is_public",
                "t"."published_at",
                "t"."created_at",
                "t"."updated_at",
                EXISTS
  (SELECT (1) AS "a"
   FROM "videos_history" U0
   WHERE (U0."user_id" IS NULL
          AND U0."video_id" = "t"."id")
   LIMIT 1) AS "is_viewed",
                EXISTS
  (SELECT (1) AS "a"
   FROM "videos_favorite" U0
   WHERE (U0."user_id" IS NULL
          AND U0."video_id" = "t"."id")
   LIMIT 1) AS "is_favorited",
                EXISTS
  (SELECT (1) AS "a"
   FROM "videos_track" U0
   INNER JOIN "videos_playlist" U1 ON (U0."playlist_id" = U1."id")
   WHERE (U1."is_wl"
          AND U1."user_id" IS NULL
          AND U0."video_id" = "t"."id")
   LIMIT 1) AS "is_wl"
FROM (
  (SELECT "videos_video".*
   FROM "videos_video"
   WHERE ("videos_video"."is_public"
      AND "videos_video"."published_at" <= '2022-01-03 05:20:16.725884+00:00'
      AND "videos_video"."title" LIKE '%word%')
   ORDER BY "videos_video"."published_at" DESC
   LIMIT 20)
UNION
  (SELECT "videos_video".*
   FROM "videos_video"
   LEFT OUTER JOIN "videos_video_tags" ON ("videos_video"."id" = "videos_video_tags"."video_id")
   LEFT OUTER JOIN "videos_tag" ON ("videos_video_tags"."tag_id" = "videos_tag"."id")
   WHERE ("videos_video"."is_public"
      AND "videos_video"."published_at" <= '2022-01-03 05:20:16.725884+00:00'
      AND "videos_tag"."name" LIKE '%word%')
   ORDER BY "videos_video"."published_at" DESC
   LIMIT 20)
) AS t
ORDER BY "t"."published_at" DESC
LIMIT 20;

Я думал использовать .raw(), но не знаю, как совместить его с пагинацией DRF. Как я могу переписать его в набор запросов?

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