Django, how to use subquery values into outer query

I have a query that uses the values from a subquery. While I'm able to write the subquery in django, I can't write the main query. Here's a little example (the schema is 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)

As you can see the outer query uses the inner value typeofdevices_count while joining with the subquery id.

About the inner query is quite simple:

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

What about the main query? I tried with the following:

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')

But I got this error, it seems it doesn't recognize the subquery annotation typeofdevices_count

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

Can you help me?

Back to Top