mysql - 在sequelize中使用groupby来获取分组项的数组

Question

我正在使用 sequelize(4.42.0) 和express。我有两个表 categories 和 main_categories ，它们具有以下关联:

db.main_categories.hasMany(db.categories);
db.categories.belongsTo(db.main_categories);

我尝试使用以下sequelize命令获取在其主类别下分组的所有类别的列表:

      var _categoryList = await models.main_categories.findAll({include: [{model: models.categories}], group:['main_categories.id']});
 res.send({category_list: _categoryList});

上述sequelize代码的输出是:

{
    "category_list": [
        {
            "id": 1,
            "name": "Physics",
            "created_at": null,
            "updated_at": null,
            "categories": [
                {
                    "id": 1,
                    "name": "Mechanics",
                    "created_at": "2019-03-21T03:39:48.000Z",
                    "updated_at": "2019-03-21T03:39:48.000Z",
                    "main_category_id": 1
                }
            ]
        },
        {
            "id": 2,
            "name": "Chemistry",
            "created_at": null,
            "updated_at": null,
            "categories": [
                {
                    "id": 6,
                    "name": "General and Physical Chemistry",
                    "created_at": "2019-03-21T03:42:54.000Z",
                    "updated_at": "2019-03-21T03:42:54.000Z",
                    "main_category_id": 2
                }
            ]
        },
        {
            "id": 3,
            "name": "Zoology",
            "created_at": null,
            "updated_at": null,
            "categories": []
        },
        {
            "id": 4,
            "name": "Botany",
            "created_at": null,
            "updated_at": null,
            "categories": []
        }
    ]
}

我的预期输出是每个 main_category 内的类别数组将包含属于同一主类别的所有类别的详细信息，而不是上面输出中所示的仅一个类别。

我的 main_categories 表包含数据:

我的categories表包含数据:

Answer 1

所以我后来发现，由于关联已经被声明，我们只需要包含模型而不需要任何 groupby 子句。所以对我有用的最终代码是:

var _categoryList = await models.main_categories.findAll({include: [{model: models.categories}]});