title: django annotate and count: how to filter the ones to include in count
body: <p>Given a queryset, I add the count of related objects (ModelA) with the following:</p>
  
  <pre><code>qs = User.objets.all()
  qs.annotate(modela__count=models.Count('modela'))
  </code></pre>
  
  <p>However, is there a way to count the ModelA that only meet a criteria? For example, count the ModelA where deleted_at is null?</p>
  
  <p>I have tried two solutions which do not properly work.</p>
  
  <p><strong>1) As @knbk suggested, use filter before you annotate.</strong></p>
  
  <pre><code>qs = User.objects.all().filter(modela__deleted_at__isnull=True).annotate(modela__count=models.Count('modela', distinct=True))
  </code></pre>
  
  <p>Here is the simplified version of the query generated by django:</p>
  
  <pre><code>SELECT COUNT(DISTINCT "modela"."id") AS "modela__count", "users".*
  FROM "users"
  LEFT OUTER JOIN "modela" ON ( "users"."id" = "modela"."user_id" ) 
  WHERE "modela"."deleted_at" IS NULL 
  GROUP BY "users"."id"
  </code></pre>
  
  <p>The problem comes from the WHERE clause.
  Indeed, there is a LEFT JOIN but the later WHERE conditions forced it to be a plain JOIN. I need to pull the conditions up into the JOIN clause to make it work as intended.</p>
  
  <p>So, instead of </p>
  
  <pre><code>LEFT OUTER JOIN "modela" ON ( "users"."id" = "modela"."user_id" ) 
  WHERE "modela"."deleted_at" IS NULL
  </code></pre>
  
  <p>I need the following which works when I execute it directly in plain SQL.</p>
  
  <pre><code>LEFT OUTER JOIN "modela" ON ( "users"."id" = "modela"."user_id" ) 
  AND "modela"."deleted_at" IS NULL
  </code></pre>
  
  <p>How can I change the queryset to get this without doing a raw query?</p>
  
  <p><strong>2) As others suggested, I could use a conditional aggregation.</strong></p>
  
  <p>I tried the following:</p>
  
  <pre><code>qs = User.objects.all().annotate(modela__count=models.Count(Case(When(modela__deleted_at__isnull=True, then=1))))
  </code></pre>
  
  <p>which turns into the following SQL query:</p>
  
  <pre><code>SELECT COUNT(CASE WHEN "modela"."deleted_at" IS NULL THEN 1 ELSE NULL END) AS "modela__count", "users".*
  FROM "users" LEFT OUTER JOIN "modela" ON ( "users"."id" = "modela"."user_id" )
  GROUP BY "users"."id"
  </code></pre>
  
  <p>By doing that, I get all the users (so the LEFT JOIN works properly) but I get "1" (instead of 0) for <code>modela__count</code> for all the users who don't have any ModelA at all.
  Why do I get 1 and not 0 if there is nothing to count?
  How can that be changed?</p>