title: SQL select from generate_series, filtering by user_id removes series?
body: <p>I have a complicated (to me) SQL query in postgresql 9.2.4 using generate_series and multiple joins. I need to sum the reps for all exercises on a particular day from the exercise table, and make sure that those exercises belong to workouts done by the current user. Finally I need to join that table to a series to display missing dates (using generate_series).</p>
  
  <p>My thoughts were to select the series in the from clause and then left join the series to a subquery that had the results of an inner join between the exercises and workouts table. For example, I have the following query:</p>
  
  <pre><code>SELECT 
      DISTINCT date_trunc('day', series.date)::date as date,
      sum(COALESCE(reps, 0)) OVER WIN,
      array_agg(workout_id) OVER WIN as ids     
  FROM (
      select generate_series(-22, 0) + current_date as date
  ) series 
  LEFT JOIN (
      exercises INNER JOIN workouts 
      ON exercises.workout_id = workouts.id
  ) 
  ON series.date = exercises.created_at::date 
  WINDOW 
     WIN AS (PARTITION BY date_trunc('day', series.date)::date)
  ORDER BY date ASC;
  </code></pre>
  
  <p>This gives the following output:</p>
  
  <pre><code>    date    | sum |                           ids                           
  ------------+-----+---------------------------------------------------------
   2013-04-27 |   0 | {NULL}
   2013-04-28 | 432 | {49,48,47,46,45,44,43,42,41,38,37,36,36,36,36,35,34,33}
   2013-04-29 |   0 | {NULL}
   2013-04-30 |  20 | {50}
   2013-05-01 |   0 | {NULL}
   2013-05-02 |   0 | {NULL}
   2013-05-03 |   0 | {NULL}
   2013-05-04 |   0 | {NULL}
   2013-05-05 |   0 | {NULL}
   2013-05-06 |   0 | {NULL}
   2013-05-07 |  40 | {51,51}
   2013-05-08 |   0 | {NULL}
   2013-05-09 |   0 | {NULL}
   2013-05-10 |   0 | {NULL}
   2013-05-11 |   0 | {NULL}
   2013-05-12 |   0 | {NULL}
   2013-05-13 |   0 | {NULL}
   2013-05-14 |   0 | {NULL}
   2013-05-15 |   0 | {NULL}
   2013-05-16 |  20 | {52}
   2013-05-17 |   0 | {NULL}
   2013-05-18 |   0 | {NULL}
   2013-05-19 |   0 | {NULL}
  (23 rows)
  </code></pre>
  
  <p>However, I want to filter by certain conditions:</p>
  
  <pre><code>WHERE workouts.user_id = 5
  </code></pre>
  
  <p>for example.</p>
  
  <p>But if I put a WHERE clause into the query above with that condition, the output is like this: </p>
  
  <pre><code>    date    | sum |                           ids                           
  ------------+-----+---------------------------------------------------------
   2013-04-28 | 432 | {49,48,47,46,45,44,43,42,41,38,37,36,36,36,36,35,34,33}
   2013-04-30 |  20 | {50}
   2013-05-07 |  40 | {51,51}
   2013-05-16 |  20 | {52}
  (4 rows)
  </code></pre>
  
  <p>The series goes away. </p>
  
  <p>How can I filter by user_id and keep the series? Any help would be much appreciated.</p>