Counting associated entries with Sequelize

Question

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; }; 

Answer 1

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'] }) 

Answer 2

For Counting associated entries with Sequelize

Location.findAll({     attributes: {          include: [[Sequelize.fn('COUNT', Sequelize.col('sensors.location')), 'sensorCounts']]      }, // Sequelize.col() should contain a attribute which is referenced with parent table and whose rows needs to be counted     include: [{         model: Sensor, attributes: []     }],     group: ['sensors.location'] // groupBy is necessary else it will generate only 1 record with all rows count }) 

Note :

Some how, this query generates a error like sensors.location is not exists in field list. This occur because of subQuery which is formed by above sequelize query.

So solution for this is to provide subQuery: false like example

Location.findAll({         subQuery: false,         attributes: {              include: [[Sequelize.fn('COUNT', Sequelize.col('sensors.location')), 'sensorCounts']]          },         include: [{             model: Sensor, attributes: []         }],         group: ['sensors.location']     }) 

Note: **Sometime this could also generate a error bcz of mysql configuration which by default contains only-full-group-by in sqlMode, which needs to be removed for proper working.

The error will look like this..**

Error : Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.table.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

So to resolve this error follow this answer

SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

Now this will successfully generate all associated counts

Hope this will help you or somebody else!