SQL-запрос Django содержит повторяющийся подзапрос при использовании фильтров с аннотациями и подзапросами

У меня есть модель Camp, которая хранит даты в виде массива строк в поле Json. Я пытаюсь оптимизировать запрос к базе данных для получения объектов Camp с будущими датами.

class Camp(models.Model):
    dates = models.JSONField(default=None, blank=True, null=True, validators= [datesListValidator])

Я использую аннотации и подзапросы, чтобы отфильтровать экземпляры этой модели, в которых ни одна из дат не равна или не превышает значения текущая дата (в принципе, мне нужны только лагеря, у которых есть дата в будущем). Я использую аннотации и подзапросы, чтобы получить "max_date" из дат для каждого лагеря, а затем отфильтровать лагеря на основе "max_date"

Я пытаюсь оптимизировать этот процесс фильтрации, так как получение всех лагерей и последующая фильтрация их в Python слишком медленны для моего варианта использования, и с течением времени количество лагерей, которые необходимо отфильтровать, будет только увеличиваться.

Решение, к которому я пришел, дает мне нужные результаты, но сгенерированный SQL-запрос содержит несколько запусков одного и того же подзапроса, который мне не нужен. Я бы хотел избежать использования необработанного SQL и хотел бы найти способ достичь желаемого результата с помощью функций django ORM. Я использую PostgreSQL и готов использовать любые встроенные функции, которые он предоставляет.

Я создал эту функцию, чтобы получить нужные мне лагеря

def get_future_camps():
    """
    Retrieves all Camp objects where the latest date in the 'dates' JSONB field is today or in the future.
    Returns a QuerySet with only relevant fields.
    """
    today_str = now().date().isoformat()

    camps_with_max = Camp.objects.annotate(
        max_date=Subquery(
            Camp.objects.filter(
                id=OuterRef("id")
            ).annotate(
                extracted_dates=JsonbArrayElementsText(F("dates"))
            ).values_list("extracted_dates", flat=True).order_by("-extracted_dates")[:1]
        )
    )

    return camps_with_max.filter(
        max_date__gte=today_str
    ).only("id", "pk")

# This only works for Postgres
class JsonbArrayElementsText(Func):
    """
    A custom function to extract elements from a JSONB array as text.
    """
    function = 'jsonb_array_elements_text'
    output_field = CharField()

Это сгенерировало следующий SQL-запрос

SELECT "my_project_camp"."id",

       (

        SELECT jsonb_array_elements_text(U0."dates") AS "extracted_dates"

          FROM "my_project_camp" U0

         WHERE U0."id" = "my_project_camp"."id"

         ORDER BY 1 DESC

         LIMIT 1

       ) AS "max_date"

  FROM "my_project_camp"

 WHERE (

        SELECT jsonb_array_elements_text(U0."dates") AS "extracted_dates"

          FROM "my_project_camp" U0

         WHERE U0."id" = "my_project_camp"."id"

         ORDER BY 1 DESC

         LIMIT 1

       ) >= '''2025-03-08'''

При этом используется один и тот же подзапрос для получения "max_date" дважды. Просмотрев его, я обнаружил, что предложения WHERE вычисляются перед SELECT, поэтому вы не можете использовать псевдонимы в предложении WHERE

Мой вопрос таков: есть ли способ удалить дополнительный вызов подзапроса? Я использую only, поэтому мне не нужно использовать поле max_date вне самого фильтра, и весь смысл этого был в том, чтобы оптимизировать получение будущих лагерей

У меня есть модель Camp, которая хранит даты в виде массива строк в поле Json.

вернитесь к моделированию. Большие двоичные объекты в формате JSON - это не хорошая идея <<[django-антипаттерны] если вам нужно работать с частями большого двоичного объекта, это нарушение первой нормальной формы (1NF) [wiki] в реляционных базах данных.

Работайте с дополнительной моделью с помощью:

class Camp(models.Model):
    # no dates
    # …
    pass


class CampDate(models.Model):
    camp = models.ForeignKey(Camp, on_delete=models.PROTECT)
    date = models.DateField()

Это также гарантирует, что date является действительной датой, и позволит сохранить ее в базе данных более компактно.

и извлеките с помощью:

from django.utils.timezone import now

today_str = now().date()
Camp.objects.filter(campdate__date__gte=today_str).distinct()
Вернуться на верх