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.

Solution

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

Comments

[Adiii]

you can put where clause in each include

[Nict]

@Adiii That gives me an unknown column 'Table2.Table3.Table4.Table4id' in field list error.

[Adiii]

what do you need? actually, i did not get your question if u need where clause in inner include then let me know

[Adiii]

var user = require('../models/').table1; var user = require('../models/').table2; include: [{ model: table1, required: true, include: [ { model: table2, required: true, where:{condtion} } ] }]

[Adiii]

docs.sequelizejs.com/en/latest/docs/models-usage/#eager-load‌​ing

[Nict]

All the models are included, couldn't find anything related to my question in the docs which is why I've taken to SO.

[Adiii]

but the way you putting where in inner include​ its not valid according to doc

[Nict]

Thanks, you're completely correct. Sorry... I followed your direction and also changed the way I assigned the where clauses (by looking through the code a little closer). Completely missed out on my own train of thought...

[Adiii]

can i post that to accept as a answer ;)

[Nict]

Yeah, man, of course. I'll happily give you points :D

[Adiii]

hehe okay check it this will help you to make option then use in your class