Django, как использовать значения подзапроса во внешнем запросе

У меня есть запрос, который использует значения из подзапроса. В то время как я могу написать подзапрос в django, я не могу написать основной запрос. Вот небольшой пример (схема department <-- person <-- device --> model):

SELECT 
    department.id,
    AVG(subquery.typeofdevices_count) AS avg_typeofdevice_count_per_department
FROM department 
INNER JOIN person ON (department.id = person.department_id) 
INNER JOIN device ON (person.id = device.person_id) 
INNER JOIN (
    SELECT 
        model.id AS id,
        count(DISTINCT device.location_id) filter (WHERE device.type = 'something') AS typeofdevices_count,
    FROM model 
    LEFT OUTER JOIN device ON (model.id = device.model_id)
    WHERE ('some-conditions...')
    GROUP BY model.id,

) AS subquery ON (subquery.id = device.model_id)

Как видите, внешний запрос использует внутреннее значение typeofdevices_count при объединении с подзапросом id.

Внутренний запрос довольно прост:

subquery = Model.objects.annotate(typeofdevices_count=
               Count('device__location_id',
                     filter=Q(device__type='something'), 
                     distinct=True
               )
            ).filter('some-conditions...').values('id', 'typeofdevices_count')

А как насчет основного запроса? Я пытался сделать следующее:

Department.objects.annotate(avg_typeofdevice_count_per_department=
    Avg('persons__devices__model__typeofdevices_count',
        filter=Q(persons__devices__model__in=subquery)
    )
).values('id', 'avg_typeofdevice_count_per_department')

Но я получил эту ошибку, похоже, что он не распознает аннотацию подзапроса typeofdevices_count

FieldError: Cannot resolve keyword 'typeofdevices_count' into field. Choices are:...

Можете ли вы мне помочь?

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

Кроме того, ваш сырой SQL не совсем корректен, но из него в целом понятно, что вы хотели сделать. Вот что-то вроде этого, что может дать ожидаемый результат, написанный с помощью django ORM:

from django.db import models

subquery = (
    Device.objects
    .select_related('model')
    .filter(
        #  device.person_id=person.id
        person_id=models.OuterRef('id'),
        #  and other conditions
    )
    #  group by model.id
    .values('model__id')
    .annotate(
        typeofdevices_count=models.Count(
            'id',
            filter=models.Q(type='something'),
            distinct=True,
        ),
    )
    .values('typeofdevices_count')
)
queryset = (
    Person.objects
    .select_related('department')
    .annotate(devices_count=models.Subquery(subquery))
    .values('department__id')
    .annotate(avg_typeofdevice_count_per_department=models.F('devices_count'))
    .values('department__id', 'avg_typeofdevice_count_per_department')
)

Возможно, я где-то допустил ошибку. Вы должны проверить это! Он сгенерирует немного другой запрос, чем ваш SQL, но результаты должны быть такими, как ожидалось - для каждого отдела вычислить среднюю стоимость устройств определенного типа.

ОБНОВЛЕНО

Есть несколько проблем с вашими ORM-запросами.

Первая проблема заключается в том, что ваш подзапрос возвращает более одного столбца в качестве результата:

subquery = Model.objects.annotate(typeofdevices_count=
               Count('device__location_id',
                     filter=Q(device__type='something'), 
                     distinct=True
               )
            ).filter('some-conditions...').values('id', 'typeofdevices_count')  # it has to be one thing.

Вторая проблема - это то, что вы видите в ошибке, ORM пыталась искать поле typeofdevices_count, но такого поля не существует. Для дальнейшей ссылки на аннотированный подзапрос следовало написать что-то вроде этого:

...
.annotate(result=models.Subquery(queryset=...))
.annotate(models.Avg(models.F(result)))
...
Вернуться на верх