Django-ORM - passing annotated column names into a Subquery?

Question

I have constructed the following query "chain":

survey_results = OrganisationSurveyResult.objects.filter(user=user)

survey_results = survey_results.filter(
    created_date__in=survey_results.values(
        'user', 'organisation_survey'
    ).annotate(
        latest_created_date=Max('created_date')
    ).values(
        'latest_created_date'
    )
).annotate(
    module=F('organisation_survey__survey__module'),
    score=F('survey_result__normalized_score'),
    rank=F('survey_result__rank_class'),
).values(
    'module', 'score', 'rank', 'created_date'
)

Which returns the following entries when iterated:

{'created_date': datetime.datetime(2019, 8, 14, 15, 10, 54, 214754, tzinfo=<UTC>), 'module': UUID('151cb049-65c5-4278-8133-610859be6f34'), 'score': 95, 'rank': 'Low'}
{'created_date': datetime.datetime(2019, 8, 14, 15, 10, 54, 220694, tzinfo=<UTC>), 'module': UUID('7c5848eb-3090-4819-8eac-166367ab0e65'), 'score': 91, 'rank': 'Low'}
{'created_date': datetime.datetime(2019, 8, 14, 15, 10, 54, 226334, tzinfo=<UTC>), 'module': UUID('b894c988-af3d-4ecb-a2a1-c42225b64b71'), 'score': 100, 'rank': 'Low'}
{'created_date': datetime.datetime(2019, 8, 14, 15, 10, 54, 232898, tzinfo=<UTC>), 'module': UUID('22edce11-f78e-4b2e-a1de-1ecc7f0518c9'), 'score': 59, 'rank': 'Medium'}

The request-response cycle for this API endpoint is 51-89ms. The DB query time is 24-38ms. It was originally 1280ms as I was using a prefetch_related method and looping.

It's also passed all tests. It has four record entries for a single user (there are only four modules). Each date in the list of entries is also the last SurveyResult for that module and user. Happy so far.

However, my next step is for each of the above entries in the list, I need to obtain the following:

for survey_result in survey_results.iterator():
    resource = Resource.objects.filter(module=survey_result['module'], rank_class=survey_result['rank'])

So, I am adding an extra 4 queries to this endpoint. Could be more in time as more modules are added.

The total bathing time for adding this pushes the query time to 44-111ms from 24-38ms.

Would anyone know a nifty way of cutting the 4 queries into one? Perhaps extending the original query somehow with some clever annotations?

I was maybe thinking maybe utilising some sort of Subquery? But how could I pass in the annotated fields from above into the Subquery?

survey_results = OrganisationSurveyResult.objects.filter(user=user)

survey_results = survey_results.filter(
    created_date__in=survey_results.values(
        'user', 'organisation_survey'
    ).annotate(
        latest_created_date=Max('created_date')
    ).values(
        'latest_created_date'
    )
).annotate(
    module=F('organisation_survey__survey__module'),
    rank_class=F('survey_result__rank_class'),
).annotate(
    resource=Subquery(
        Resource.objects.filter(
            module=module,
            rank_class=rank_class,
        ).order_by('?')[0]
    )
).values(
    'resource'
)

Django version: 2.2.2

@dirkgroten Thanks for this. However, I am getting the following validation error: `["'OuterRef(module)' is not a valid UUID."]`. It looks like this could be a bug with using UUID fields as primary keys? — Micheal J. Roberts, Aug 20 '19 at 15:27
@dirkgroten Just to confirm, the above error is being thrown when using `Resource.objects.filter(module=OuterRef(OuterRef('module')), rank_class=OuterRef(OuterRef('rank_class')))` inside the `Subquery`. — Micheal J. Roberts, Aug 20 '19 at 15:31
why the double wrap? `module=OuterRef('module')` should do the trick. Sorry, my first comment might have been a bit misleading: the parenthesis was an example for how to use it (it's not inside the code)... — dirkgroten, Aug 20 '19 at 15:32
@dirkgroten Also, the single wrap gives a new error: `This queryset contains a reference to an outer query and may only be used in a subquery.` — Micheal J. Roberts, Aug 20 '19 at 15:34
my comments was supposed to be read: use `OuterRef` (e.g. `OuterRef('module')`) — dirkgroten, Aug 20 '19 at 15:34
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/198192/discussion-between-wind-up-lord-vexxos-and-dirkgroten). — Micheal J. Roberts, Aug 20 '19 at 15:38
@dirkgroten Please see above comment - both the single and double `OuterRef()` methods do not work. See error above. — Micheal J. Roberts, Aug 20 '19 at 15:41

Django-ORM - passing annotated column names into a Subquery?

0 Answers0