title: Using Django Window Functions on a Filtered QuerySet
body: <p>I ran into a surprising conundrum with Window Functions on Filtered QuerySets.</p>
  <p>Consider two models: mymodel and relatedmodel where there is a one to many relationship (i.e. relatedmodel has a ForeignKey into mymodel).</p>
  <p>I am using something like this:</p>
  <pre><code>window_lag = Window(expression=Lag(&quot;pk&quot;), order_by=order_by)
  window_lead = Window(expression=Lead(&quot;pk&quot;), order_by=order_by)
  window_rownnum = Window(expression=RowNumber(), order_by=order_by)
  qs1 = mymodel.objects.filter(relatedmodel__field=XXX)
  qs2 = qs1.annotate(row=window_rownnum, prior=window_lag, next=window_lead)
  qs3 = qs2.filter(pk=myid)
  </code></pre>
  <p>which returns a lovely result for the object pk=myid. I now know its position in the filtered list and its prior and next and I use this to great effect in browsing filtered lists.</p>
  <p>Significantly len(qs1) = len(qs2) is the size of the list and len(qs3)=1</p>
  <p>Alas I just discovered this breaks badly when the filter is less specific:</p>
  <pre><code>window_lag = Window(expression=Lag(&quot;pk&quot;), order_by=order_by)
  window_lead = Window(expression=Lead(&quot;pk&quot;), order_by=order_by)
  window_rownnum = Window(expression=RowNumber(), order_by=order_by)
  qs1 = mymodel.objects.filter(relatedmodel__field__contains=X)
  qs2 = qs1.annotate(row=window_rownnum, prior=window_lag, next=window_lead)
  qs3 = qs2.filter(pk=myid)
  </code></pre>
  <p>In this instance, qs2 suddenly has more rows in it that qs1! And len(qs2)&gt;len(qs1).</p>
  <p>Which totally breaks the browser in a sense (as prior and next are not reliable any more). The extra rows are duplicate mymodel objects, wherever more than one relatedmodel object matches the criterion.</p>
  <p>I've traced this to the generated SQL.</p>
  <p>This is the form of qs1's SQL:</p>
  <pre><code>SELECT DISTINCT 
    &quot;mymodel&quot;.&quot;id&quot;, &quot;mymodel&quot;.&quot;order_by&quot; ....
  FROM &quot;mymodel&quot;
  INNER JOIN &quot;relatedmodel&quot; ON (&quot;mymodel&quot;.&quot;id&quot; = &quot;relatedmodel&quot;.&quot;mymodel_id&quot;)
  WHERE (&quot;related_model&quot;.&quot;field&quot;::text LIKE '%X%')
  ORDER BY &quot;mymodel&quot;.&quot;order_by&quot; ASC
  </code></pre>
  <p>And this runs fine in my database engine as a SQL query, and returns the same number of rows that Django sees. All good.</p>
  <p>Then the SQL that qs2 produces resembles:</p>
  <pre><code>SELECT DISTINCT 
    ROW_NUMBER() OVER (ORDER BY &quot;mymodel&quot;.&quot;order_by&quot; ASC) AS &quot;row&quot;,
    LAG(&quot;mymodel&quot;.&quot;id&quot;, 1) OVER (ORDER BY &quot;mymodel&quot;.&quot;order_by&quot; ASC) AS &quot;prior,
    LEAD(&quot;mymodel&quot;.&quot;id&quot;, 1) OVER (ORDER BY &quot;mymodel&quot;.&quot;order_by&quot; ASC) AS &quot;next&quot;,
    &quot;mymodel&quot;.&quot;id&quot;, &quot;mymodel&quot;.&quot;order_by&quot; ....
  FROM &quot;mymodel&quot;
  INNER JOIN &quot;relatedmodel&quot; ON (&quot;mymodel&quot;.&quot;id&quot; = &quot;relatedmodel&quot;.&quot;mymodel_id&quot;)
  WHERE (&quot;related_model&quot;.&quot;field&quot;::text LIKE '%X%')
  ORDER BY &quot;mymodel&quot;.&quot;order_by&quot; ASC
  </code></pre>
  <p>And again this produces the same number of rows as I see in Django but that's more than qs1 when the relatedmodel matches more than once.</p>
  <p>I can doctor the SQL and get what I want, namely by windowing after filtering:</p>
  <pre><code>SELECT 
    ROW_NUMBER() OVER (ORDER BY &quot;mymodel&quot;.&quot;order_by&quot; ASC) AS &quot;row&quot;
    LAG(&quot;mymodel&quot;.&quot;id&quot;, 1) OVER (ORDER BY &quot;mymodel&quot;.&quot;order_by&quot; ASC) AS &quot;prior,
    LEAD(&quot;mymodel&quot;.&quot;id&quot;, 1) OVER (ORDER BY &quot;mymodel&quot;.&quot;order_by&quot; ASC) AS &quot;next&quot;,
    &quot;id&quot;, &quot;order_by&quot; ....
  FROM (
    SELECT DISTINCT 
      &quot;mymodel&quot;.&quot;id&quot;, &quot;mymodel&quot;.&quot;order_by&quot; ....
    FROM &quot;mymodel&quot;
    INNER JOIN &quot;relatedmodel&quot; ON (&quot;mymodel&quot;.&quot;id&quot; = &quot;relatedmodel&quot;.&quot;mymodel_id&quot;)
    WHERE (&quot;related_model&quot;.&quot;field&quot;::text LIKE '%X%')
    ORDER BY &quot;mymodel&quot;.&quot;order_by&quot; ASC
  ) AS QS
  </code></pre>
  <p>Which works beautifully and returns the same number of rows as qs1 again.</p>
  <p>Adding just one window  function inside the SELECT causes the DISTINCT to fail for some reason. DISTINCT works fine without a window function (returning only unique mymodel rows) but adding a window function breaks this.</p>
  <p>Using the filter as a subquery on the window function works.</p>
  <p>And Django supports subqueries, but I can't find a way to apply them here.</p>
  <p>So I wonder if there is a way to do this. Namely, to apply the annotation as a wrapper around the QuerySet rather than as additional columns in the queryset.</p>