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

Question

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.

Vivek Doshi · Accepted Answer · 2018-09-25T11:33:23.670

18

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

edited Sep 25 '18 at 11:33

answered Sep 25 '18 at 11:27

Vivek Doshi

56,649
12
110
122

Getting error : `]^ SyntaxError: Unexpected token ]` at `attributes: [ 'id','title', 'is_enabled', [ sequelize.fn('count', sequelize.col('company_users.id')) ,'user_count']], ]` – Ansari Maksud Sep 25 '18 at 11:32
1

remove one extra `]` , inside attribute , please check updated answer. – Vivek Doshi Sep 25 '18 at 11:33
2

It's works fine, Only remaning thing is `group:['companies.id']` – Ansari Maksud Sep 25 '18 at 11:41

Kasir Barati · Answer 2 · 2020-07-27T07:01:05.980

this is something that works for me:

await PostModel.findAll({
  group: ['posts.id'],
  order: [['createdAt', 'DESC']],
  include: [
    {
      model: CategoryModel,
      attributes: ['title'],
      where: { title: categoryTitle }
    },
    { model: CommentModel },
    { model: UserModel, attributes: ['fullname', 'id'] }
  ],
  attributes: [
    'title', 'content', 'description', 'thumbnail', 'baner', 'createdAt', 'updatedAt',
    [Sequelize.fn('COUNT', 'comment.id'), 'commentsCounter']
  ]
});

Associations:

Post M:N Category
Post 1:N Comment
Post N:1 User

please note to this part 'comment.id' not 'comments.id'.

if you use 'comments.id' it throws this error for you: SequelizeDatabaseError: missing FROM-clause entry for table "comments"

MY MODELS - UPDATE: and comment

const { sequelize } = require('./index');
const { Model, DataTypes } = require('sequelize');
class CommentModel extends Model {};
CommentModel.init({
    id: {
        primaryKey: true,
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4
    },
    content: {
        type: DataTypes.TEXT,
        allowNull: false
    }
}, {
    sequelize,
    modelName: 'comments',
    timestamps: true,
    paranoid: false
});

module.exports = CommentModel;

How did you set your models? This solution doesn't work for me — cylee, Jul 27 '20 at 05:43
hello @cylee. I add my models in my answer. you can see them — Kasir Barati, Jul 27 '20 at 06:49

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

2 Answers2

Linked