title: sequelize Nested include with where clause
body: <p>I'm using sequelize for some filtering.</p>
  
  <p>My current table structure:</p>
  
  <ul>
  <li>Table1 holds Items (which has images) and Users (irrelevant)</li>
  <li>Table1 has a direct relationship to Table2 through Table2id on Table1 (not to Table3)</li>
  <li>Table2 has a direct relationship to Table3 through Table3id on Table2 (not to Table4)</li>
  <li>Table3 has a direct relationship to Table4 through Table4id on Table3</li>
  </ul>
  
  <p>I want to filter on Table3 and Table4 as well, considering I can only filter on Table2 using the top-level where-clause.</p>
  
  <p>The way I fill out my where condition is just using a base object:</p>
  
  <pre><code>var Table2id       = parseInt(req.query.Table2id) || null,
      Table3id       = parseInt(req.query.Table3id) || null,
      Table4id       = parseInt(req.query.Table4id) || null,
      whereCondition = { deleted: 0 }
  
  if (Table2id) { whereCondition['table2id'] = Table2id }
  if (Table3id) { whereCondition['table3id'] = Table3id }
  if (Table4id) { whereCondition['table4id'] = Table4id }
  
  Table1.findAndCountAll({
          limit: limit,
          offset: offset,
          order: 'created_at DESC',
          where: whereCondition,
          include: [
              {
                  model: User,
              }, {
                  model: Item,
                  include: [
                      {
                          model: Image
                      }
                  ]
              }, {
                  model: Table2,
                  include: [
                      {
                          model: Table3,
                          include: [
                              {
                                  model: Table4,
                              }
                          ]
                      }
                  ]
              }
          ],
  }).then(function (results) { res.json(results) })
  </code></pre>
  
  <p>I tried using some hacks I discovered like <code>whereCondition['$Table3.table3id$'] = Table3id</code> but to no avail.</p>
  
  <p>How can I filter on nested includes? Is there another way I can structure the query so I don't have to have nested includes, but still retain this data structure (is there even a better way to structure this than what I've thought of)?</p>
  
  <p>edit: So I would like to both be able to sort on the tables included, and have at least one parameter set in the top-level where-clause (like deleted = 0).</p>
  
  <p>I've tried modifying the query as follows:</p>
  
  <pre><code>var Table2id       = parseInt(req.query.Table2id) || null,
      Table3id       = parseInt(req.query.Table3id) || null,
      Table4id       = parseInt(req.query.Table4id) || null,
      whereCondition = { deleted: 0 },
      extraWhereCondition = {}
  
  if (Table2id) { whereCondition['table2id'] = Table2id } // figured this can be left alone in this particular case (as it works in top-level where clause)
  if (Table3id) { extraWhereCondition['table3id'] = Table3id }
  if (Table4id) { extraWhereCondition['table4id'] = Table4id }
  
  Table1.findAndCountAll({
          limit: limit,
          offset: offset,
          order: 'created_at DESC',
          where: whereCondition,
          include: [
              {
                  model: User,
              }, {
                  model: Item,
                  include: [
                      {
                          model: Image
                      }
                  ]
              }, {
                  model: Table2,
                  include: [
                      {
                          model: Table3,
                          where: extraWhereCondition,
                          include: [
                              {
                                  model: Table4,
                                  where: extraWhereCondition,
                              }
                          ]
                      }
                  ]
              }
          ],
  }).then(function (results) { res.json(results) })
  </code></pre>
  
  <p>But this gives me an error that Table2.Table3.Table4.table4id is unknown in field list.</p>