Don't work hierarchy neested with MySQL (findAll)

[gp67git]

Hi (sorry for my english)

the problem is don't extract children nested and if call model.getChildren() the error is: (node:10432) UnhandledPromiseRejectionWarning: TypeError: model.getChildren is not a function

Can you help me ? Thanks Giuly

Use this software version:

• MySQL 8.0 in local installation

and this node package:

• "express": "^4.17.1",
• "mysql2": "^2.1.0",
  • "sequelize": "^5.21.3",
  • "sequelize-hierarchy": "^2.0.4"

My configuration options Sequilize :

    dialect: 'mysql',
    host: 'localhost',
    port: 3306,
    pool: { max: 5, min: 0, acquire: 30000, idle: 10000}, 
    forceDrop: true,
    logging: false,
    define: {timestamps: true, paranoid: true, freezeTableName: true} 

My model is:

const Sequelize = require('sequelize');
require('sequelize-hierarchy')(Sequelize);
const sequelizeConn = require('../dbconnection');

const TypeSkill = require('../models/TypeSkill.model');

const nametable = 'Skill'; 

  const attributes = {
    id: {
      type: Sequelize.INTEGER(11),
      allowNull: false,
      primaryKey: true,
      autoIncrement: true,
      comment: null,
      field: "id"
    },
    name: {
      type: Sequelize.STRING(45),
      allowNull: false,
      comment: null,
      field: "name",
      unique: "name_UNIQUE"
    },
    description: {
      type: Sequelize.STRING(500),
      allowNull: true,
      defaultValue: null,
      comment: null,
      field: "description"
    },
    isCategory: {
      type: Sequelize.BOOLEAN,
      allowNull: true,
      defaultValue: null,
      comment: null,
      field: "isCategory"
    },
    createdBy: {
      type: Sequelize.STRING(45),
      allowNull: true,
      defaultValue: null,
      comment: null,
      field: "createdBy"
    },
    updatedBy: {
      type: Sequelize.STRING(45),
      allowNull: true,
      defaultValue: null,
      comment: null,
      field: "updatedBy"
    },
    deletedBy: {
      type: Sequelize.STRING(45),
      allowNull: true,
      defaultValue: null,
      comment: null,
      field: "deletedBy"
    }
  };
  const options = {
    comment: "",
    indexes: [{
      name: "fk_Skill_TypeSkill1_idx",
      unique: false,
      fields: ["idTypeSkill"]
  };
  const Skill = sequelizeConn.define(nametable, attributes, options);
  Skill.isHierarchy(); 
  Skill.belongsTo(TypeSkill, {as: 'TypeSkill', foreignKey: 'idTypeSkill'}  )
  module.exports = Skill;

Table created:

skill

skillancestor

relation created

Controller JS is:

const model = require('../models/Skill.model');
const TypeSkill = require('../models/TypeSkill.model');

const util = require('../UtilResponse');

const controller = {};

 controller.listAll = async (req, res) => {
   model.rebuildHierarchy(); //with o without the JSON result is same 
   model.findAll( {include: [  //with only hierarchy:true ... the result is same
     {model: model, as: 'children', hierarchy: true, 
    attributes: ['id', 'name', 'parentId', 'hierarchyLevel']} ]
     })
       .then(data => {
         if (data.length > 0) {
           return util.setSuccessList(res, data)
         } else {
           return util.setEmptyList(res, data)
         }
       })
       .catch(err =>  {return util.setError(res, err)})
 };
module.exports = controller;

The result JSON is:

{
    "status": "success",
    "message": "Found ",
    "data": [
        {
            "id": 1,
            "name": "Full Stack",
            "description": "Full Stack Description",
            "isCategory": true,
            "createdBy": null,
            "updatedBy": null,
            "deletedBy": null,
            "createdAt": "2020-01-24T11:08:28.000Z",
            "updatedAt": "2020-01-24T11:10:22.000Z",
            "deletedAt": null,
            "hierarchyLevel": 1,
            "parentId": null,
            "idTypeSkill": null,
            "children": [
                {
                    "id": 2,
                    "name": "Back End ",
                    "parentId": 1,
                    "hierarchyLevel": 2
                },
                {
                    "id": 3,
                    "name": "Front End ",
                    "parentId": 1,
                    "hierarchyLevel": 2
                },
                {
                    "id": 6,
                    "name": "Javascript",
                    "parentId": 1,
                    "hierarchyLevel": 2
                }
            ]
        },
        {
            "id": 2,
            "name": "Back End ",
            "description": "Back End  Description",
            "isCategory": true,
            "createdBy": null,
            "updatedBy": null,
            "deletedBy": null,
            "createdAt": "2020-01-24T11:08:38.000Z",
            "updatedAt": "2020-01-24T11:10:22.000Z",
            "deletedAt": null,
            "hierarchyLevel": 2,
            "parentId": 1,
            "idTypeSkill": null,
            "children": [
                {
                    "id": 8,
                    "name": "ReactJS",
                    "parentId": 2,
                    "hierarchyLevel": 3
                }
            ]
        },
        {
            "id": 3,
            "name": "Front End ",
            "description": "Front End Description",
            "isCategory": true,
            "createdBy": null,
            "updatedBy": null,
            "deletedBy": null,
            "createdAt": "2020-01-24T11:08:48.000Z",
            "updatedAt": "2020-01-24T11:10:22.000Z",
            "deletedAt": null,
            "hierarchyLevel": 2,
            "parentId": 1,
            "idTypeSkill": null,
            "children": [
                {
                    "id": 7,
                    "name": "CSS",
                    "parentId": 3,
                    "hierarchyLevel": 3
                }
            ]
        },
        {
            "id": 4,
            "name": "PM",
            "description": "PM Description",
            "isCategory": true,
            "createdBy": null,
            "updatedBy": null,
            "deletedBy": null,
            "createdAt": "2020-01-24T11:08:56.000Z",
            "updatedAt": "2020-01-24T11:10:22.000Z",
            "deletedAt": null,
            "hierarchyLevel": 1,
            "parentId": null,
            "idTypeSkill": null,
            "children": [
                {
                    "id": 5,
                    "name": "Scrum",
                    "parentId": 4,
                    "hierarchyLevel": 2
                }
            ]
        },
        {
            "id": 5,
            "name": "Scrum",
            "description": "Scrum Description",
            "isCategory": false,
            "createdBy": null,
            "updatedBy": null,
            "deletedBy": null,
            "createdAt": "2020-01-24T11:09:17.000Z",
            "updatedAt": "2020-01-24T11:10:22.000Z",
            "deletedAt": null,
            "hierarchyLevel": 2,
            "parentId": 4,
            "idTypeSkill": null,
            "children": []
        },
        {
            "id": 6,
            "name": "Javascript",
            "description": "Javascript Language",
            "isCategory": false,
            "createdBy": null,
            "updatedBy": null,
            "deletedBy": null,
            "createdAt": "2020-01-24T11:09:28.000Z",
            "updatedAt": "2020-01-24T11:10:22.000Z",
            "deletedAt": null,
            "hierarchyLevel": 2,
            "parentId": 1,
            "idTypeSkill": null,
            "children": []
        },
        {
            "id": 7,
            "name": "CSS",
            "description": "CSS",
            "isCategory": false,
            "createdBy": null,
            "updatedBy": null,
            "deletedBy": null,
            "createdAt": "2020-01-24T11:09:39.000Z",
            "updatedAt": "2020-01-24T11:10:22.000Z",
            "deletedAt": null,
            "hierarchyLevel": 3,
            "parentId": 3,
            "idTypeSkill": null,
            "children": []
        },
        {
            "id": 8,
            "name": "ReactJS",
            "description": "ReactJS",
            "isCategory": false,
            "createdBy": null,
            "updatedBy": null,
            "deletedBy": null,
            "createdAt": "2020-01-24T11:09:50.000Z",
            "updatedAt": "2020-01-24T11:10:22.000Z",
            "deletedAt": null,
            "hierarchyLevel": 3,
            "parentId": 2,
            "idTypeSkill": null,
            "children": []
        }
    ]
}

[overlookmotel]

@gpnick67 Thanks for providing so much detail. I edited your post to format the code examples so the are more readable.

I will have a look at this and come back to you as soon as I can (it will be a few days though).

[overlookmotel]

OK, I am finding it hard to understand your example. There is some stuff to do with TypeSkill model which doesn't seem to be relevant, and the controller code is also extraneous. So it's harder than it needs to be to read through your example and understand it. It would be easier to help you if you could reduce the example.

But here's some first suggestions:

model.getChildren() is not a valid method

.getChildren() should be called on a model instance, not the model itself.

const skill = await Skill.findOne( { where: { id: 1 } } );
const children = await skill.getChildren();

model.rebuildHierarchy() is async

So you should do await model.rebuildHierarchy(). Without the await, it's going to be in middle of rebuilding the hierarchy table when your .findAll() query runs => chaos.

I don't think this is relevant to your problem, but just letting you know.

Simpler query

Can you please try model.findAll( { hierarchy: true } ) (without the include section etc) and see if that works?

Or, if you want to use include, it should be as: 'descendents' not as: 'children'.

Children is just the direct children of that node (which is what you got). Descendents is children + children's children + children's children's children + to infinity (which is what I think you want).

model.findAll( {
  include: [
    {
      model: model,
      as: 'descendents', // NOT 'children'
      hierarchy: true, 
      attributes: ['id', 'name', 'parentId', 'hierarchyLevel']
    }
  ]
} ).then( /* ... */ )

I'm pretty sure this was your problem so I'm closing this issue. But feel free to comment again if I am wrong.