Counting associated entries with Sequelize
mysql, node.js, sequelize.js
Solution
Use `findAll()` with `include()` and `sequelize.fn()` for the `COUNT`:
Location.findAll({
attributes: {
include: [[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]]
},
include: [{
model: Sensor, attributes: []
}]
});
Or, you may need to add a `group` as well:
Location.findAll({
attributes: {
include: [[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]]
},
include: [{
model: Sensor, attributes: []
}],
group: ['Location.id']
})
Problem
I have two tables, `locations` and `sensors`. Each entry in `sensors` has a foreign key pointing to `locations`. Using Sequelize, how do I get all entries from `locations` and total count of entries in `sensors` that are associated with each entry in `locations`? Raw SQL: ``` SELECT `locations`.*, COUNT(`sensors`.`id`) AS `sensorCount` FROM `locations` JOIN `sensors` ON `sensors`.`location`=`locations`.`id`; GROUP BY `locations`.`id`; ``` Models: ``` module.exports = function(sequelize, DataTypes) { var Location = sequelize.define("Location", { id: { type: DataTypes.INTEGER.UNSIGNED, primaryKey: true }, name: DataTypes.STRING(255) }, { classMethods: { associate: function(models) { Location.hasMany(models.Sensor, { foreignKey: "location" }); } } }); return Location; }; module.exports = function(sequelize, DataTypes) { var Sensor = sequelize.define("Sensor", { id: { type: DataTypes.INTEGER.UNSIGNED, primaryKey: true }, name: DataTypes.STRING(255), type: { type: DataTypes.INTEGER.UNSIGNED, references: { model: "sensor_types", key: "id" } }, location: { type: DataTypes.INTEGER.UNSIGNED, references: { model: "locations", key: "id" } } }, { classMethods: { associate: function(models) { Sensor.belongsTo(models.Location, { foreignKey: "location" }); Sensor.belongsTo(models.SensorType, { foreignKey: "type" }); } } }); return Sensor; }; ```