mysql - 获取层次结构数据nodejs-sequelizejs-mysql

Question

这是表格文件夹

id  name                    isSub   parentid    status
------------------------------------------------------
1   Main Folder1            0       NULL        1
2   Main Folder2            0       NULL        1
3   Sub Main Fol2           1       2           1
4   Sub Main Fol2           1       3           1
5   Sub Main Fol1           1       1           1
6   Sub Main Fol1           1       1           1
7   Main Folder3            0       NULL        1
8   Sub Main Fol1           1       1           1
------------------------------------------------------

这是我用来获取层次结构的表。我正在使用nodejs-sequelizejs。这是我的模型

sequelize.define('Folders', {
        id: {
            type: Sequelize.INTEGER,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        name: {
            type: Sequelize.STRING(100),
            allowNull: false,
            validate: {
                len: [1, 100]
            }
        },
        isSub: {
            type: Sequelize.ENUM(),
            values: ['0', '1'],
            allowNull: false,
            defaultValue: '0'
        },
        parentid: {
            type: Sequelize.INTEGER,
            allowNull: true,
            validate: {
                isNumeric: true
            }
        },
        status: {
            type: Sequelize.ENUM(),
            values: ['0', '1', '2'],
            allowNull: false,
            defaultValue: '1'
        },
    },
    {
        tableName: 'folders'
    }
);

Folders.associate = function(models) {
    models.Folders.hasMany(models.Folders, {
      foreignKey: 'parentid',
      as: 'children'
    });
};

还有我的 Controller 函数来获取列表

models.Folders.findAll({
        where: {
            isSub : 1,
            status : '1',
        },
        include: [{
          model: models.Folders,
          as: 'children',
          where: {
              status: '1'
          },
            required: false
        }],
        required: false
    }).then((data) => {

    data.map((foldData) => {
        return Object.assign({}, {
            fid: foldData['id'],
            fName: foldData['name'],
            fisSub: (foldData['isSub'] === '1')? 'Yes': 'No',
            subFolders:
            foldData['children'].map((sfoldData, index) => {

                return Object.assign({}, {
                    sfid: sfoldData['id'],
                    sfName: sfoldData['name'],
                    sisSubFold: (sfoldData['isSub']) === '1'? 'Yes': 'No',
                })
            }),
        });
    });
});

通过上面的代码，我得到的API调用结果如下

[
    {
        "fid": "2",
        "fName": "Main Folder2",
        "fisSub": "No",
        "subCategory": [
            {
                "sfid": "3",
                "sfName": "Sub Main Fol2",
                "sisSubFold": "Yes"
            }
        ]
    },
    {
        "fid": "1",
        "fName": "Main Folder1",
        "fisSub": "No",
        "subCategory": [
            {
                "sfid": "5",
                "sfName": "Sub Main Fol1",
                "sisSubFold": "Yes"
            },
            {
                "sfid": "6",
                "sfName": "Sub Main Fol1",
                "sisSubFold": "Yes"
            },
            {
                "sfid": "8",
                "sfName": "Sub Main Fol1",
                "sisSubFold": "Yes"
            }
        ]
    },
    {
        "fid": "7",
        "fName": "Main Folder3",
        "fisSub": "No",
        "subCategory": []
    }
]

这里的"fid": "2"在表中有两级层次结构，但我只得到第一级别。

请更正我的代码以获得层次结构。

注意:会有很多层次结构，不仅仅是第二/第三/很快的级别。我搜索了很多东西，但没有得到任何正确的东西。请帮助我摆脱这个困境。

提前致谢

Answer 1

你想要做的是递归查询，而Sequelize尚不支持它，这里有一个提案https://github.com/sequelize/sequelize/issues/4890解决方法是使用 raw 函数编写您自己的 CTE 请求。