title: Counting associated entries with Sequelize
body: <p>I have two tables, <code>locations</code> and <code>sensors</code>. Each entry in <code>sensors</code> has a foreign key pointing to <code>locations</code>. Using Sequelize, how do I get all entries from <code>locations</code> and total count of entries in <code>sensors</code> that are associated with each entry in <code>locations</code>?</p>
  
  <p>Raw SQL:</p>
  
  <pre><code>SELECT 
      `locations`.*,
      COUNT(`sensors`.`id`) AS `sensorCount` 
  FROM `locations` 
  JOIN `sensors` ON `sensors`.`location`=`locations`.`id`;
  GROUP BY `locations`.`id`;
  </code></pre>
  
  <p>Models:</p>
  
  <pre><code>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;
  };
  </code></pre>