Django с использованием подзапроса в annotate: Как получить все строки, соответствующие условию фильтрации

У меня есть две модели с M2M field. Поскольку не будет никаких обновлений или удалений (просто нужно читать данные из БД), я хочу иметь одно обращение к БД для получения всех необходимых данных. Я использовал prefetch_related с Prefetch, чтобы иметь возможность фильтровать данные, а также иметь отфильтрованные объекты в кэшированном списке с помощью to_attr. Я попытался достичь того же результата, используя annotate вместе с Subquery. но здесь я не могу понять, почему аннотированный файл содержит только одно значение вместо списка значений. давайте рассмотрим код, который у меня есть:

  • некоторые маршруты могут иметь более одной особой точки (экземпляры точек с is_special=True).

models.py

class Route(models.Model):
    indicator = models.CharField()

class Point(models.Model):
    indicator = models.CharField()
    route = models.ManyToManyField(to=Route, related_name="points")
    is_special=models.BooleanField(default=False)

views.py

routes = Route.objects.filter(...).prefetch_related(
        Prefetch(
            "points",
            queryset=Point.objects.filter(is_special=True),
            to_attr="special_points",
        )
    )

это будет работать, как и ожидалось, но приведет к тому, что для получения данных о точках будет выполняться отдельный запрос к базе данных. В следующем коде я попытался использовать подзапрос, чтобы получить один запрос к базе данных.

routes = Route.objects.filter(...).annotate(
        special_points=Subquery(
            Point.objects.filter(route=OuterRef("pk"), is_special=True).values("indicator")
        )

Проблема в том, что во втором подходе при печати будет либо один, либо ни одного индикатора особых точек route_instance.special_points, даже если при использовании prefetch распечатанный результат для того же экземпляра Route показывает, что особых точек еще две.

  • Я знаю, что в первом подходе route_instance.special_points будет содержать экземпляры Point, а не их индикаторы, но в этом и заключается проблема.

  • Я проверил SQL код подзапроса и не обнаружил никаких признаков ограничения в запросе, так как я не использовал нарезку и в коде python. но опять же результат ограничен либо одной (если существует одна или более), либо ни одной, если нет ни одной особой точки.

Вот как я проверяю подключение к базе данных

# Enable query counting
from django.db import connection

connection.force_debug_cursor = True

route_analyzer(data, err)

# Output the number of queries
print(f"Total number of database queries: {len(connection.queries)}")
for query in connection.queries:
    print(query["sql"])
# Disable query counting
connection.force_debug_cursor = False

С помощью GPT у меня есть сырой sql-код, который дает результат:

  • он основан на некотором коде python, так что это не чистый шаблон.
SELECT "general_route"."id", "general_route"."indicator",
       (SELECT GROUP_CONCAT(U0."indicator", ', ')
        FROM "points_point" U0
        INNER JOIN "points_point_route" U1 ON (U0."id" = U1."point_id")
        WHERE (U1."route_id" = "general_route"."id" AND U0."is_special")
        ) AS "special_points",
       (SELECT GROUP_CONCAT(U0."indicator", ', ')
        FROM "points_point" U0
        INNER JOIN "points_point_route" U1 ON (U0."id" = U1."point_id")
        WHERE (U1."route_id" = "general_route"."id" AND U0."indicator" IN ('CAK', 'NON'))
       ) AS "all_points"
    FROM "general_route"
    WHERE ("general_route"."indicator" LIKE 'OK%' OR "general_route"."indicator" LIKE 'OI%')
    ORDER BY "general_route"."indicator" ASC

Ответ на мой вопрос

После просмотра сгенерированного SQL кода django-ORM я попытался создать пользовательскую версию метода GroupConcat, чтобы конкатенировать indicator поле всех отфильтрованных объектов вместе (затем просто использовать split для генерации их списка). тут я понял, что наследование от Aggregate (от django.db. models), приведет к тому, что сгенерированный SQL-код будет содержать ненужный оператор GROUP BY, который также включает все поля целевой модели, поэтому вывод будет включать только одно значение (если таковое имеется) или вообще ничего. Даже если GroupConcat хорошо реализован, эта group-by закончится своеобразным циклом, который перезапустит GroupConcat и скормит ему только одно значение в каждом rosw, так что не останется ничего из предыдущих строк, чтобы быть конкатенированным с новой строкой. Но наследование от Func (от django.db.models) избавило нас от этого лишнего и ненужного GROUP BY, так что запрос получит все строки и передаст ожидаемые столбцы всех строк в GroupConcat сразу, и в конце дня будет получен ожидаемый результат.

from django.db.models import Func, OuterRef, Subquery
from main.models import Point, Route


class CustomConcat(Func):
    function = "GROUP_CONCAT"

    def __init__(self, expression, delimiter=",", **extra):
        super().__init__(expression, delimiter=delimiter, **extra)


# Subquery to fetch related indicators for each Route
special_points_indicators = Point.objects.filter(route=OuterRef("pk"), special=True).values("indicator")

# Annotate Route queryset with the subquery using the custom aggregate function
routes = Route.objects.annotate(
    special_points=Subquery(
        special_points_indicators.annotate(indicators=CustomConcat("indicator", delimiter=",")).values(
            "indicators"
        )
    )
)

# Accessing the translated SQL query
sql_query = str(routes.query)
print(sql_query)

# Accessing each Route object along with its associated boundary point indicators
for f in routes:
    print(f, f.special_points)
Вернуться на верх