I am using sequelize to query on a sqlserver database. I have two tables:

data: columns - id, name, team, type

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

and a relation

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

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

My query is:

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

My result looks like this:

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

I want that instead of the history array I will have the count of history objects in each one. The query in sql is:

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

I am using sequelize to query on a sqlserver database. I have two tables:

data: columns - id, name, team, type

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

and a relation

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

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

My query is:

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

My result looks like this:

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

I want that instead of the history array I will have the count of history objects in each one. The query in sql is:

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

• javascript
• node.js
• sequelize.js

• Improved formatting – Sheldore Commented Aug 30, 2018 at 9:23

1 Answer 1

You can do it this way:

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