Sequelize 'hasMany' associated(model) count in attributes in query execution

associations, mysql, sequelize.js

Solution

You can use `sequelize.fn` , try to run below query :

Company.findAll({
    attributes: [
        'id', 'title', 'is_enabled',
        [sequelize.fn('count', sequelize.col('company_users.id')) ,'user_count'] // <---- Here you will get the total count of user
    ],
    include: [
        {
            model: sqConn.CompanyUser,
            attributes: [] // <----- Make sure , this should be empty
        }
    ],
    group: ['companies.id'] // <---- You might require this one also
}).then(data => { 
    console.log(data); // <---- Check the output
})

Problem

Sequelize version: 4.22.6, MySql version:5.7.8 I want to 'hasMany' associated(CompanyUser) count in attibutes(at place of _user_count_) in query execution ``` /** * Company user associate with Company with belongsTo relation */ `CompanyUser.belongsTo(Company, { foreignKey: 'company_id', targetKey: 'id'});` /** * Company associate with Company user with hasMany relation */ `Company.hasMany(CompanyUser, { foreignKey: 'company_id', sourceKey: 'id'});` `return Company.findAll({ attributes: [ 'id', 'title', 'is_enabled', '_user_count_' ] include: [ { model: sqConn.CompanyUser, attributes: ['id'], }, { model: sqConn.CompanyLogo, attributes:['file_object'], } ], }).then(function(model) { return sequelize.Promise.resolve(model); }).catch(function(err) { return sequelize.Promise.reject(err); });` ``` Simple MySQL query with left-join works fine and give count.

Original source