title: Django conditional Subquery aggregate
body: <p>An simplified example of my model structure would be</p>
  
  <pre><code>class Corporation(models.Model):
      ...
  
  class Division(models.Model):
      corporation = models.ForeignKey(Corporation)
  
  class Department(models.Model):
      division = models.ForeignKey(Division)
      type = models.IntegerField()
  </code></pre>
  
  <p>Now I want to display a table that display corporations where a column will contain the number of departments of a certain type, e.g. <code>type=10</code>. Currently, this is implemented with a helper on the <code>Corporation</code> model that retrieves those, e.g.</p>
  
  <pre><code>class Corporation(models.Model):
      ...
      def get_departments_type_10(self):
          return (
              Department.objects
              .filter(division__corporation=self, type=10)
              .count()
          )
  </code></pre>
  
  <p>The problem here is that this absolutely murders performance due to the N+1 problem.</p>
  
  <p>I have tried to approach this problem with <code>select_related</code>, <code>prefetch_related</code>, <code>annotate</code>, and <code>subquery</code>, but I havn't been able to get the results I need.</p>
  
  <p>Ideally, each <code>Corporation</code> in the queryset should be annotated with an integer <code>type_10_count</code> which reflects the number of departments of that type.</p>
  
  <p>I'm sure I could do something with raw sql in <code>.extra()</code>, but the docs announce that it is going to be deprecated (I'm on Django 1.11)</p>
  
  <p><strong>EDIT: Example of raw sql solution</strong></p>
  
  <pre><code>corps = Corporation.objects.raw("""
  SELECT
  *,
  (
      SELECT COUNT(*)
      FROM foo_division div ON div.corporation_id = c.id
      JOIN foo_department dept ON dept.division_id = div.id
      WHERE dept.type = 10
  ) as type_10_count
  FROM foo_corporation c
  """)
  </code></pre>