Recursive include Sequelize?

I have category that can have child categories

And when I'm doing findAll I want to include all of those nested, but I don't know the depth.

var includeCondition = { 
                         include: [
                            { 
                               model: models.categories,
                               as:'subcategory', nested: true 
                            }]
                       };

models.categories.findAll(includeCondition)
        .then(function (categories) {
            resolve(categories);
        })
        .catch(function (err) {
            reject(err);
        })
});

The result brings me only one level nested include.

[  
   {  
      dataValues:{  

      },
      subcategory:{  
         model:{  
            dataValues:{  

            }
            // no subcategory here            
         }
      }
   }
]

Can I somehow make sequalize include those nested subcategories ?

Answers:

Answer

There are few solutions if found for this first one is more complicated but will give better performance:

This one is about implementing hierarchical data structure in MySQL I like the guide here

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

The one that is named The Nested Set Model.

The second solution that I actually implemented by myself is recursive expanding, this one uses lots of mysql requests and I believe can be improved, but it's a fast one and works well. The thing is to use for each category function like this

var expandSubcategories = function (category) {
    return new promise(function (resolve, reject) {
        category.getSubcategories().then(function (subcategories) {
            //if has subcategories expand recursively inner subcategories
            if (subcategories && subcategories.length > 0) {
                var expandPromises = [];
                _.each(subcategories, function (subcategory) {
                    expandPromises.push(expandSubcategories(subcategory));
                });

                promise.all(expandPromises).then(function (expandedCategories) {
                    category.subcategories = [];

                    _.each(expandedCategories, function (expandedCategory) {
                        category.subcategories.push(expandedCategory);
                    }, this);


                    //return self with expanded inner
                    resolve(category);
                });

            } else {
                //if has no subcategories return self
                resolve(category);
            }
        });
    });
};

So it's going through the categories and expanding them recursively.

Maybe this will help someone as well.

Answer

There is a node module which handle it : sequelize-hierarchy
It adds column parentId and hierarchyLevel to your table. As an example, this is what I did to order employees skills in a tree.
Skills could be "Macro" -> "Excel" -> "Office" -> "Computer"
database.js:

const  Sequelize = require('sequelize');
require('sequelize-hierarchy')(Sequelize);
const sequelize = new Sequelize("stackoverflow", null, null, {
  dialect: "sqlite",
  storage: "database.db"
});
sequelize.sync().then(() => {console.log("Database ready");});
module.exports = sequelize;

skill.js:

module.exports = (sequelize, DataTypes) => {
  const Skill = sequelize.define("skill", {
    name:           DataTypes.STRING,
  });
  Skill.isHierarchy();
  return Skill;
};

Then in your controller:

Skill.findAll().then(skills => {
  res.send(skills); // Return a list
});
Skill.findAll({ hierarchy: true }).then(skills => {
  res.send(skills); // Return a tree
});
Answer

Suppose you have 5 different models A, B, C, D, E and A is associated with B, B with C and so on. So while fetching data for A you can get the all the nested subcategory hierarchy by using

include: [{ all: true, nested: true }]

Example: A.findAll(where:{// add conditions}, { include: [{ all: true, nested: true }]});

Tags

Recent Questions

Top Questions

Home Tags Terms of Service Privacy Policy DMCA Contact Us

©2020 All rights reserved.