CODEWITHSUNDEEP
pythondjango
Ben Davis
Ben Davis

Reputation: 13830

Django complains that query must be used in subquery, when it is being used in a subquery

I have the following custom QuerySet method for a model called Program:

def with_volunteer_stats(self):
    from programs.models import Session
    volunteers_needed = ExpressionWrapper(
        F('num_student_seats') / F('student_volunteer_ratio'),
        output_field=models.IntegerField())

    sessions = (
        Session.objects.filter(program_id=OuterRef('pk'))
        .annotate(num_volunteers=Count('volunteer_attendances__volunteer', distinct=True))
        .order_by('num_volunteers')
    )
    qs = self.annotate(
        volunteers_needed=volunteers_needed,
        least_volunteers=Subquery(sessions.values('num_volunteers')[0])
    ).annotate(
        remaining_volunteers_needed=(F('volunteers_needed') - F('num_volunteers'))
    )
    return qs

When running this, I get the exception:

ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

The traceback shows the exception occurring on the evaluation of the line using Subquery. This is very close to the example in the Django documentation on how to use Subquery. Any ideas what I'm doing wrong?

Upvotes: 5

Views: 2724

Answers (1)

Bobby Eickhoff
Bobby Eickhoff

Reputation: 2596

I ran into the same problem. Now that I have a better idea what's happening, I thought I'd add an explanation for posterity.

The problem appears to be here:

Subquery(sessions.values('num_volunteers')[0])
#                                         ^^^

The intent is that Subquery is being given a QuerySet, but what's actually happening is that the item accessor is forcing evaluation prematurely. In other words, this yields a QuerySet:

sessions.values('num_volunteers')

But this forces evaluation to yield an object:

sessions.values('num_volunteers')[0]

This means the sessions.values('num_volunteers') QuerySet is evaluated outside of the context of the Subquery. So the error message is correct, if unclear.

Per mfrackowiak's comment below, the Django documentation offers a recommendation for how to limit the subquery to a single row:

sessions.values('num_volunteers')[:1]

Upvotes: 10

Related Questions