javascript - 需要计算一个连接表行sequelize

Question

我sequelize用来查询sqlserver数据库。我有两张桌子：

data: columns - id, name, team, type

history:columns - id, node, date, status, data_id(foreignkey)

和一个关系

history.belongsTo(data, {foreignKey: 'data_id'}

data.hasMany(history, {foreignKey: 'data_id'})

我的查询是：

dataModel.findAll({
    attributes: ['name'],
    include: [{
        model:historyModel
    }]
})

我的结果如下所示：

[
    {
         name: "1",
         history: [
             {
                 ...
             }
         ]
    },
    {
         name: "2",
         history: [
             {
                 ...
             }
         ]
    }
]`

我希望用它而不是历史数组来计算每个历史对象的数量。中的查询sql是：

select data.name, count(history.data_id) count 
from history 
inner join data on data.id=history.data_id 
group by history.data_id, data.name

标签： javascriptnode.jssequelize.js

Answer 1

你可以这样做：

dataModel.findAll({
    attributes: { 
        include: [[Sequelize.fn("COUNT", Sequelize.col("history.data_id")), "historyModelCount"]] 
    },
    include: [{
        model: historyModel, attributes: []
    }],
    group: ['data.id']
});