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

Answer 1

你可以这样做:

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