Subquery и Exists в Django ORM | Курс Django ORM урок 3.4

Цель урока

Разобрать подзапросы в Django ORM: Subquery для использования результата одного QuerySet внутри другого, OuterRef для корреляции с внешним запросом и Exists для проверки наличия связанных объектов. Понять когда подзапросы эффективнее JOIN и когда наоборот.

Необходимые знания

Урок 3.3: annotate(), aggregate()
Урок 3.2: F expressions
Базовое понимание подзапросов в SQL

Проблема которую решают подзапросы

Рассмотрим задачу: для каждого продукта показать дату последнего заказа, в котором он встречался.

Через annotate() с Max():

from django.db.models import Max

Product.objects.annotate(
    last_ordered=Max("order_items__order__created_at")
)

SELECT shop_product.*,
       MAX(shop_order.created_at) AS last_ordered
FROM shop_product
LEFT OUTER JOIN shop_orderitem ON shop_product.id = shop_orderitem.product_id
LEFT OUTER JOIN shop_order ON shop_orderitem.order_id = shop_order.id
GROUP BY shop_product.id;

Работает. Но что если нужно не дату, а сам объект заказа или несколько полей из него? Тогда annotate() с агрегатной функцией не подойдет, она возвращает одно скалярное значение.

Для таких случаев используется Subquery.

Subquery - подзапрос как скалярное значение

Subquery оборачивает QuerySet и использует его как подзапрос в SQL. Важно: подзапрос должен возвращать ровно одно поле и ровно одну строку (скалярный подзапрос).

from django.db.models import OuterRef, Subquery

# Для каждого продукта найти id последнего заказа
latest_order = Order.objects.filter(
    items__product=OuterRef("pk")  # ссылка на id продукта из внешнего запроса
).order_by("-created_at").values("id")[:1]

products = Product.objects.annotate(
    latest_order_id=Subquery(latest_order)
)

SELECT shop_product.*,
       (
           SELECT shop_order.id
           FROM shop_order
           INNER JOIN shop_orderitem ON shop_order.id = shop_orderitem.order_id
           WHERE shop_orderitem.product_id = shop_product.id
           ORDER BY shop_order.created_at DESC
           LIMIT 1
       ) AS latest_order_id
FROM shop_product
ORDER BY shop_product.name;

Ключевые моменты:

OuterRef("pk"): ссылка на поле внешнего запроса. "pk" означает id продукта из основного SELECT. Это делает подзапрос коррелированным, он выполняется отдельно для каждой строки внешнего запроса.

.values("id")[:1]: values() гарантирует что подзапрос возвращает одно поле, [:1], одну строку. Оба условия обязательны для скалярного подзапроса.

Несколько полей через несколько Subquery

Если нужно несколько полей из связанной таблицы, делайте отдельный Subquery для каждого:

latest_order_qs = Order.objects.filter(
    items__product=OuterRef("pk")
).order_by("-created_at")

products = Product.objects.annotate(
    latest_order_id=Subquery(latest_order_qs.values("id")[:1]),
    latest_order_status=Subquery(latest_order_qs.values("status")[:1]),
    latest_order_date=Subquery(latest_order_qs.values("created_at")[:1]),
)

SELECT shop_product.*,
       (SELECT id FROM shop_order ... LIMIT 1) AS latest_order_id,
       (SELECT status FROM shop_order ... LIMIT 1) AS latest_order_status,
       (SELECT created_at FROM shop_order ... LIMIT 1) AS latest_order_date
FROM shop_product;

Три коррелированных подзапроса, три отдельных SELECT для каждой строки продукта. Это может быть дорого на больших таблицах. Сравним с альтернативами ниже.

OuterRef - ссылка на внешний запрос

OuterRef, это F() для внешнего запроса. Он используется только внутри Subquery.

OuterRef("pk")        # id модели внешнего запроса
OuterRef("user_id")   # конкретное поле
OuterRef("category__slug")  # через связь

Вложенные OuterRef для двух уровней подзапросов:

# Подзапрос внутри подзапроса
OuterRef(OuterRef("pk"))  # ссылка на два уровня вверх

Subquery в filter()

Subquery можно использовать не только в annotate(), но и в filter(). Но сравнивать результат подзапроса со значением напрямую в filter() нельзя, нужно сначала аннотировать, потом фильтровать:

# Найти продукты у которых последний заказ был со статусом "delivered"
latest_order_status = Order.objects.filter(
    items__product=OuterRef("pk")
).order_by("-created_at").values("status")[:1]

Product.objects.annotate(
    latest_status=Subquery(latest_order_status)
).filter(latest_status="delivered")

SELECT shop_product.*
FROM shop_product
WHERE (
    SELECT shop_order.status
    FROM shop_order
    INNER JOIN shop_orderitem ON shop_order.id = shop_orderitem.order_id
    WHERE shop_orderitem.product_id = shop_product.id
    ORDER BY shop_order.created_at DESC
    LIMIT 1
) = 'delivered'
ORDER BY shop_product.name;

Но обычно для фильтрации по наличию связанных объектов проще и эффективнее Exists.

Exists - проверка наличия

Exists проверяет, существует ли хотя бы одна строка в подзапросе. Транслируется в SQL EXISTS (...).

from django.db.models import Exists, OuterRef

# Продукты у которых есть хотя бы один отзыв
reviewed_products = Review.objects.filter(product=OuterRef("pk"))

Product.objects.filter(Exists(reviewed_products))

SELECT shop_product.*
FROM shop_product
WHERE EXISTS (
    SELECT 1
    FROM shop_review
    WHERE shop_review.product_id = shop_product.id
)
ORDER BY shop_product.name;

~Exists - отрицание

# Продукты без единого отзыва
Product.objects.filter(~Exists(reviewed_products))

WHERE NOT EXISTS (
    SELECT 1
    FROM shop_review
    WHERE shop_review.product_id = shop_product.id
)

Exists с условиями

# Продукты у которых есть отзывы с rating >= 4
has_good_reviews = Review.objects.filter(
    product=OuterRef("pk"),
    rating__gte=4,
)

Product.objects.filter(Exists(has_good_reviews))

WHERE EXISTS (
    SELECT 1
    FROM shop_review
    WHERE shop_review.product_id = shop_product.id
      AND shop_review.rating >= 4
)

Exists в annotate() - булевый флаг

# Аннотировать каждый продукт флагом "есть ли хороший отзыв"
has_good_review = Exists(
    Review.objects.filter(product=OuterRef("pk"), rating__gte=4)
)

Product.objects.annotate(has_good_review=has_good_review)

SELECT shop_product.*,
       EXISTS (
           SELECT 1 FROM shop_review
           WHERE product_id = shop_product.id AND rating >= 4
       ) AS has_good_review
FROM shop_product;

Exists vs filter() через связь - сравнение

Задача: найти продукты с хотя бы одним отзывом.

Вариант 1: filter() через связь

Product.objects.filter(reviews__isnull=False).distinct()

SELECT DISTINCT shop_product.*
FROM shop_product
INNER JOIN shop_review ON shop_product.id = shop_review.product_id
ORDER BY shop_product.name;

JOIN + DISTINCT. Работает, но JOIN умножает строки, DISTINCT их убирает, лишняя работа.

Вариант 2: Exists

Product.objects.filter(
    Exists(Review.objects.filter(product=OuterRef("pk")))
)

SELECT shop_product.*
FROM shop_product
WHERE EXISTS (SELECT 1 FROM shop_review WHERE product_id = shop_product.id)
ORDER BY shop_product.name;

Нет JOIN, нет DISTINCT. PostgreSQL останавливается при первом найденном отзыве, не читает все строки.

На практике PostgreSQL может выбрать одинаковый план для обоих вариантов через оптимизатор. Но Exists более явно выражает намерение и на больших таблицах стабильнее.

Subquery vs annotate() с агрегацией, когда что

annotate() с агрегацией: один запрос с GROUP BY:

# Один LEFT JOIN + GROUP BY
Product.objects.annotate(review_count=Count("reviews"))

Эффективно когда нужно агрегатное значение (count, sum, avg) по всем связанным строкам.

Subquery: коррелированный подзапрос для каждой строки:

# Для каждого продукта, отдельный подзапрос
Product.objects.annotate(
    latest_review_text=Subquery(
        Review.objects.filter(product=OuterRef("pk"))
        .order_by("-created_at")
        .values("text")[:1]
    )
)

Эффективно когда нужна конкретная строка (не агрегат), например последний отзыв, или специфическое значение.

Правило:

Нужен COUNT, SUM, AVG, используйте annotate() с агрегатной функцией
Нужна конкретная строка или поле из связанной таблицы, используйте Subquery
Нужно только проверить наличие, используйте Exists

Реальный пример: продукты со скидкой

Допустим у нас есть модель Discount с активными скидками на продукты:

class Discount(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE, related_name="discounts")
    percent = models.PositiveIntegerField()
    is_active = models.BooleanField(default=True)
    valid_until = models.DateTimeField()

Задача: аннотировать каждый продукт текущим процентом скидки, если есть.

from django.utils import timezone

active_discount = Discount.objects.filter(
    product=OuterRef("pk"),
    is_active=True,
    valid_until__gt=timezone.now(),
).order_by("-percent").values("percent")[:1]

products = Product.objects.annotate(
    current_discount=Subquery(active_discount)
)

# Продукты со скидкой
discounted = products.filter(current_discount__isnull=False)

# Продукты без скидки
no_discount = products.filter(current_discount__isnull=True)

SELECT shop_product.*,
       (
           SELECT percent
           FROM shop_discount
           WHERE product_id = shop_product.id
             AND is_active = true
             AND valid_until > NOW()
           ORDER BY percent DESC
           LIMIT 1
       ) AS current_discount
FROM shop_product
WHERE (
    SELECT percent FROM shop_discount
    WHERE product_id = shop_product.id ...
    LIMIT 1
) IS NOT NULL;

Практическое задание

1) Аннотируйте каждый Order суммой его позиций (items__price * items__quantity) через Subquery. Сравните результат с полем total_price.

2) Найдите продукты без заказов через ~Exists(). Посмотрите SQL.

3) Аннотируйте каждого пользователя флагом has_delivered_order, есть ли у него хотя бы один заказ со статусом "delivered". Используйте Exists в annotate().

4) Для каждого продукта найдите имя пользователя, оставившего последний отзыв. Используйте Subquery с OuterRef и order_by("-created_at").values("user__username")[:1].

5) Сравните производительность двух вариантов поиска продуктов с отзывами:

filter(reviews__isnull=False).distinct()
filter(Exists(Review.objects.filter(product=OuterRef("pk"))))

Посмотрите SQL для обоих. Чем отличаются планы выполнения?

Возможные ошибки

Subquery возвращает более одной строки

# Ошибка: подзапрос возвращает несколько строк
Product.objects.annotate(
    review_ids=Subquery(Review.objects.filter(product=OuterRef("pk")).values("id"))
    # нет [:1] вернет все id отзывов
)
# ProgrammingError: more than one row returned by a subquery used as an expression

Всегда добавляйте [:1] для скалярного подзапроса.

Subquery возвращает более одного поля

# Ошибка: нет .values("конкретное_поле")
Product.objects.annotate(
    last_review=Subquery(
        Review.objects.filter(product=OuterRef("pk")).order_by("-created_at")[:1]
        # нет .values("поле"), вернет все поля
    )
)

Всегда используйте .values("поле") перед [:1].

OuterRef вне Subquery

# OuterRef работает только внутри Subquery
Product.objects.filter(id=OuterRef("pk"))  # ValueError

Коррелированный подзапрос на большой таблице без индекса

Если OuterRef("pk") ссылается на поле без индекса во вложенной таблице, каждая строка внешнего запроса выполняет seq scan. На таблице с миллионом строк это критично. Убедитесь что поле в WHERE подзапроса имеет индекс.

Связь со следующим уроком

В уроке 3.5 разберем Case/When, условные выражения в запросах. Они позволяют реализовать логику IF/ELIF/ELSE прямо в SQL, используются в аннотациях, обновлениях и сортировке.

<< Урок 3.3

Урок 3.5 >>

Подписывайтесь на мой Telegram канал

Если вам нужен ментор и вы хотите научиться веб-разработке, узнать подробнее