My MySQL database contains a Category Table (Maybe the "parent" column is the wrong approach here):

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

How is it possible to structure and retrieve the data with Sequelize in the following form:

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

I am using Sequelize cli. This is what I tried:

To Query Categories:

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

My Category Model:

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

Results in the following error:

"Not unique table/alias: 'Category'"

Whats the right solution to structure my Categories? Use children instead of parent?

My MySQL database contains a Category Table (Maybe the "parent" column is the wrong approach here):

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

How is it possible to structure and retrieve the data with Sequelize in the following form:

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

I am using Sequelize cli. This is what I tried:

To Query Categories:

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

My Category Model:

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

Results in the following error:

"Not unique table/alias: 'Category'"

Whats the right solution to structure my Categories? Use children instead of parent?

• javascript
• mysql
• node.js
• express
• sequelize.js

Share Improve this question Follow asked Mar 12, 2018 at 15:15 HelloWorld0815HelloWorld0815 62044 gold badges1111 silver badges3030 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 6

Try to add in Category Model:

as: 'children'

and change

belongsTo

to

hasMany

like:

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

Your Query Categories now looks like:

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