Counting associated entries with Sequelize

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

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

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!

Location.findAll({
        attributes: { 
            include: [[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]] 
        },
        include: [{
            model: Sensor, attributes: []
        }]
    });

and it works. but when i add "limit", i got error: sensors undefined