title: SQLAlchemy Left Outer Join on Dates and Orders is lost on filtering Orders by store. How can I prevent this?
body: <p>I have a working left join but as soon as I add a filter to select only Orders from a specific store, the left outer join turns into a normal join and I loose the dates where there are no orders.</p>
  <pre class="lang-py prettyprint-override"><code>db.session.query(
          Dates.day_date,
          Orders.date_created,
          Orders.sales
      ).select_from(
          Dates
      ).outerjoin(
          Orders, Dates.day_date == cast(Orders.date_created, Date)
      ).filter(
          Dates.day_date == Orders.date_created
      ).group_by(
                  Orders.date_created,
                  Dates.day_date
      ).all()
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Dates.day_date</th>
  <th>Orders.date_created</th>
  <th>Orders.sales</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021-02-01</td>
  <td>2021-02-01 19:41:17</td>
  <td>94.8</td>
  </tr>
  <tr>
  <td>2021-02-02</td>
  <td>None</td>
  <td>None</td>
  </tr>
  <tr>
  <td>2021-02-03</td>
  <td>None</td>
  <td>None</td>
  </tr>
  <tr>
  <td>2021-02-04</td>
  <td>2021-02-04 19:21:13</td>
  <td>24.8</td>
  </tr>
  <tr>
  <td>2021-02-05</td>
  <td>2021-02-05 13:44:47</td>
  <td>44.4</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>as soon as I add the filter below I loose the join and the table empty columns vanish.  How do I keep the Dates.day_date values?</p>
  <pre class="lang-py prettyprint-override"><code>.filter(
          Orders.store_id == store.id
  )
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Dates.day_date</th>
  <th>Orders.date_created</th>
  <th>Orders.sales</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021-02-01</td>
  <td>2021-02-01 19:41:17</td>
  <td>94.8</td>
  </tr>
  <tr>
  <td>2021-02-04</td>
  <td>2021-02-04 19:21:13</td>
  <td>24.8</td>
  </tr>
  <tr>
  <td>2021-02-05</td>
  <td>2021-02-05 13:44:47</td>
  <td>44.4</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>---EDIT---
  this has been solved by @van</p>
  <pre class="lang-py prettyprint-override"><code>db.session.query(
          Dates.day_date,
          Orders.date_created,
          Orders.sales
      ).select_from(
          Dates
      ).outerjoin(
          Orders, Dates.day_date == cast(Orders.date_created, Date)
      ).filter(or_(
          Orders.store_id == store.id,
          Orders.store_id == None
      )).group_by(
          Orders.date_created,
          Dates.day_date
      ).all()
  </code></pre>
  <p>The only problem I have now is that when I test this with multiple stores data. I filter for a specific store.id, but the missing rows still persist when there are None values in the store's order data I am filtering for but not in another stores. I think the &quot;Orders.store_id == None&quot; is looking for None values in all stores, is it possible to combine an 'or' plus an 'and' filter to work around this?</p>