CODEWITHSUNDEEP
sqldjangodjango-orm
Alessandro Salvetti
Alessandro Salvetti

Reputation: 104

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.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?

Upvotes: 0

Views: 60

Answers (1)

Serhii Fomenko
Serhii Fomenko

Reputation: 1055

It's hard to tell you anything without looking at your models and what foreign keys exist for the relations.

Also, your raw SQL, is not completely correct, but from it is generally clear what you wanted to do. Here is something like this, which can give the expected result written with 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')
)

I may have made a mistake somewhere. You should check it out! It will generate a slightly different query than your SQL, but the results should be as expected - for each department to calculate the average value of devices of a certain type.

UPDATED

There are several problems with your ORM queries.

The first problem is that your subquery returns more than one column as a result:

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.

The second problem is what you see in the error, ORM tried looking for the typeofdevices_count field, but such a field exists. To further reference the annotated subquery, you should have written something like this:

...
.annotate(result=models.Subquery(queryset=...))
.annotate(models.Avg(models.F(result)))
...

Upvotes: 0

Related Questions