sequelize Nested include with where clause

Question

I'm using sequelize for some filtering.

My current table structure:

• Table1 holds Items (which has images) and Users (irrelevant)
• Table1 has a direct relationship to Table2 through Table2id on Table1 (not to Table3)
• Table2 has a direct relationship to Table3 through Table3id on Table2 (not to Table4)
• Table3 has a direct relationship to Table4 through Table4id on Table3

I want to filter on Table3 and Table4 as well, considering I can only filter on Table2 using the top-level where-clause.

The way I fill out my where condition is just using a base object:

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) })

I tried using some hacks I discovered like whereCondition['$Table3.table3id$'] = Table3id but to no avail.

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)?

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).

I've tried modifying the query as follows:

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) })

But this gives me an error that Table2.Table3.Table4.table4id is unknown in field list.

Answer 1

You just need to the way to put where and you can also make option object and then pass it where you need

• put where condition in every include if need
• required true and false change joing rule

When an eager loaded model is filtered using include.where then include.required is implicitly set to true. This means that an inner join is done returning parent models with any matching children.

• sequelize called it eager-loading visit for more detail eager-loading

var Table2 = require("../models/").table2; //and other model that u need

var option = {
  limit: limit,
  offset: offset,
  order: "created_at DESC",
  where: { deleted: 0 },
  include: [
    {
      model: User,
    },
    {
      model: Item,
      required: true,

      include: [
        {
          model: Image,
        },
      ],
    },
    {
      model: Table2,
      include: [
        {
          model: Table3,
          where: { deleted: 0 },
          include: [
            {
              model: Table4,
              where: { deleted: 0 },
            },
          ],
        },
      ],
    },
  ],
};

Table1.findAndCountAll(option).then(function (results) {
  res.json(results);
});