How to count a group by query in NodeJS Sequelize

Question

In Rails I can perform a simple ORM query for the number of Likes a model has:

    @records = Model         .select( 'model.*' )         .select( 'count(likes.*) as likes_count' )         .joins( 'LEFT JOIN likes ON model.id = likes.model_id' )         .group( 'model.id' ) 

This generates the query:

SELECT  models.*, count(likes.*) as likes_count FROM "models" JOIN likes ON models.id = likes.model_id GROUP BY models.id 

In Node Sequelize, any attempt at doing something similar fails:

return Model.findAll({     group: [ '"Model".id' ],     attributes: ['id', [Sequelize.fn('count', Sequelize.col('"Likes".id')), 'likes_count']],     include: [{ attributes: [], model: Like }], }); 

This generates the query:

SELECT     Model.id,     count(Likes.id) AS likes_count,     Likes.id AS Likes.id           # Bad! FROM Models AS Model LEFT OUTER JOIN Likes     AS Likes     ON Model.id = Likes.model_id GROUP BY Model.id; 

Which generates the error:

column "Likes.id" must appear in the GROUP BY clause or be used in an aggregate function 

It's erroneously selecting likes.id, and I have no idea why, nor how to get rid of it.

Answer 1

This sequelize github issue looks totally like your case:

User.findAll({   attributes: ['User.*', 'Post.*', [sequelize.fn('COUNT', 'Post.id'), 'PostCount']],   include: [Post] }); 

Answer 2

To resolve this problem we Need to upgrade to latest version of sequelize and include raw = true, Here is How I had done after lot of iteration and off-course googling.

 getUserProjectCount: function (req, res) {         Project.findAll(             {                 attributes: ['User.username', [sequelize.fn('COUNT', sequelize.col('Project.id')), 'ProjectCount']],                 include: [                     {                         model: User,                         attributes: [],                         include: []                     }                 ],                 group: ['User.username'],                 raw:true             }         ).then(function (projects) {             res.send(projects);         });     } 

where my reference models are

//user var User = sequelize.define("User", {     username: Sequelize.STRING,     password: Sequelize.STRING });  //project var Project = sequelize.define("Project", {     name: Sequelize.STRING,     UserId:{          type:Sequelize.INTEGER,          references: {                  model: User,                   key: "id"          }     } });  Project.belongsTo(User); User.hasMany(Project); 

after migration ORM create 'Users' & 'Projects' table into my postgres server. Here is SQL Query by ORM

SELECT    "User"."username", COUNT("Project"."id") AS "ProjectCount"   FROM      "Projects" AS "Project"      LEFT OUTER JOIN "Users" AS "User" ON "Project"."UserId" = "User"."id"   GROUP BY     "User"."username";