javascript - Sequelize : Category Model hierarchical structure with self reference

Question

我的 MySQL 数据库包含一个类别表(也许“父”列在这里是错误的方法):

+----+--------------+--------+
| id |     name     | parent |
+----+--------------+--------+
|  1 | Service      | null   |
|  2 | Lifestyle    | null   |
|  3 | Food & Drink | 2      |
|  4 | Beauty       | 2      |
+----+--------------+--------+

如何使用 Sequelize 按以下形式构造和检索数据:

[
    {
        "id": 1,
        "name": "Service",
        "children": null
    },
    {
        "id": 2,
        "name": "Lifestyle",
        "children": [
                      {
                        "id": 3,
                        "name": "Food & Drink",
                        "children": null
                      },
                      {
                        "id": 4,
                        "name": "Beauty",
                        "children": null
                      },
                    ]
    }
]

我正在使用 Sequelize cli。这是我尝试过的:

查询类别:

    const result = await models.Category.findAll({
        where: {
          parent: null
        },
        include: ['Category']
      });
    res.send(result);

我的类别模型:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Category = sequelize.define(
    'Category',
    {
      name: {
        allowNull: false,
        type: DataTypes.STRING
      },
      parent: { type: DataTypes.INTEGER }
    },
    {}
  );
  Category.associate = function(models) {
    models.Category.belongsTo(models.Category, {
      onDelete: 'CASCADE',
      foreignKey: 'parent'
    });
  };
  return Category;
};

导致以下错误:

"Not unique table/alias: 'Category'"

构建我的类别的正确解决方案是什么？使用子代而不是父代？

Answer 1

尝试添加类别模型:

as: 'children'

并改变

belongsTo

至

hasMany

喜欢:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Category = sequelize.define(
    'Category',
    {
      name: {
        allowNull: false,
        type: DataTypes.STRING
      },
      parent: { type: DataTypes.INTEGER }
    },
    {}
  );
  Category.associate = function(models) {
    models.Category.hasMany(models.Category, {
      onDelete: 'CASCADE',
      foreignKey: 'parent',
      as: 'children'
    });
  };
  return Category;
};

您的查询类别现在如下所示:

const result = await models.Category.findAll({
    where: {
      parent: null
    },
    include: [{
      model: models.Category,
      as: 'children'
    }]
  });
res.send(result);