title: Nested subqueries in Django
body: <p>Getting into deep water with Subquery. I have a set of <code>Carpark</code>s. <code>Carpark</code>s have multiple <code>Booking</code>s. Bookings have many <code>BarrierActivity</code> records, which are the various coming and going events at the barriers. These are all simple FKs up the stack.</p>
  
  <p>It is possible for a booking to arrive and the barrier cameras <em>not</em> recognise it. A member of staff will buzz them in but that means the system has failed for some reason. And that's what I'm trying to do here. Work out what percentage of my bookings got in via automated means. I know there are a number of other ways of doing this, but I'd like to do this with a single subquery-based queryset.</p>
  
  <p>My aim reasonably simple. Annotate 0 or 1 to show whether or not an "entry" <code>BarrierActivity</code> exists for each <code>Booking</code>. Annotate an average of those values, per <code>Carpark</code>.</p>
  
  <p>The first part is fine. I can do a simple <code>Exists()</code> between <code>BarrierActivity</code> and <code>Booking</code> and then each booking has the 0 or 1:</p>
  
  <pre><code>successful_bas = BarrierActivity.objects.order_by().filter(
      booking=OuterRef('pk'),
      activity_type=BarrierActivity.TYPE_ANPR_BOOKING,
      direction='entry'
  ).values('booking')
  
  Booking.objects.order_by().annotate(
      entry_success=Exists(successful_bas)
  )
  </code></pre>
  
  <p>And again, that works fine. But as soon as I try to scale that up another layer (so looking at <code>Carpark</code> instead of <code>Booking</code>)... </p>
  
  <pre><code>successful_bas = BarrierActivity.objects.order_by().filter(
      booking=OuterRef('pk'),
      activity_type=BarrierActivity.TYPE_ANPR_BOOKING,
      direction='entry'
  ).values('booking')
  
  bookings = Booking.objects.order_by().filter(
      carpark=OuterRef('pk')
  ).values('carpark').annotate(
      entry_success=Exists(successful_bas)
  ).values('entry_success')
  
  Carpark.objects.order_by().annotate(
      entry_hitrate=ExpressionWrapper(
          Avg(Cast(Subquery(bookings), IntegerField())) * 100,
          output_field=FloatField()
      )
  )
  </code></pre>
  
  <p>... I get the Subquery-error-of-doom: <code>more than one row returned by a subquery used as an expression</code>. The <code>bookings</code> subquery it clearly returning too much but how do I aggregate that before it hits the outermost subquery?</p>
  
  <hr>
  
  <p>I've tried many things, but here's a reorganisation of the average to within the subquery. Same error:</p>
  
  <pre><code>successful_bas = "... as before ..."
  
  bookings = Booking.objects_standard.order_by().filter(
      carpark=OuterRef('pk')
  ).values('site').annotate(
      entry_success=Exists(successful_bas)
  ).annotate(
      entry_avg=Avg(Cast('entry_success', IntegerField()))
  ).values('entry_avg')
  
  Carpark.objects.order_by().annotate(
      entry_hitrate=ExpressionWrapper(
          Subquery(bookings, output_field=FloatField()) * 100,
          output_field=FloatField()
      )
  )
  </code></pre>