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.

Always selecting the id of a joined model is a sequelize technicality, and cannot be disabled. Try adding likes.id to your group clause — Jan Aagaard Meier, Apr 25 '15 at 18:38
The answer to this question, which was deleted by a mod, is don't use Sequelize. It frequently produces invalid SQL that crashes the query (you had one job). Knex query builder is a much better tool that offers the same light abstraction around sql that rails does. Sql is already a good abstraction, don't let Sequelize make it worse for you. — Andy Ray, Feb 26 '16 at 19:04
Well you could use `findAndCountAll` method. Please verify https://stackoverflow.com/a/52857907/1709558 — Richie, Oct 17 '18 at 15:04

score 33 · Answer 1 · edited Jan 09 '18 at 08:04

33

This sequelize github issue looks totally like your case:

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

edited Jan 09 '18 at 08:04

Karl Adler

15,780
10
70
88

answered Mar 03 '16 at 00:06

Evgeniy Chekan

2,615
1
15
23

score 12 · Answer 2 · answered Aug 04 '16 at 20:25

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

I think this is a great example of why to avoid using Sequelize at all costs. It's not just a leaky abstraction over SQL, they invent their own nonsense abstractions to fix their own software, like `"raw": true`. SQL is already a fine abstraction, Sequelize only introduces new bugs on top of it. — Andy Ray, Aug 04 '16 at 20:35
+1 for hint with "raw = true". Without that, my sequelize (version 3.27.0) was acting crazy. Simple example: `model.findAll attributes: ['name', sequelize.fn('sum', sequelize.col('value'))] group: ['name']` outputed structure grouped by "name" (thats ok) but instead of sum(value) there was some other third column, absolutely unrelated. — venca163, Nov 30 '16 at 22:20
@AndyRay can you recommend a better ORM for node? (Working with pure db drivers (e.g. sqlite) requires tones of boilerplate) — Dziad Borowy, Apr 28 '18 at 21:21
The `raw` bug report: https://github.com/sequelize/sequelize/issues/5481 — Ciro Santilli OurBigBook.com, Jul 26 '22 at 11:12

score 1 · Answer 3 · answered Feb 20 '23 at 12:32

What worked for me counting column A and grouping by column B

const noListingsPerRetailer = Listing.findAll({
        attributes: [
          'columnA',
          [sequelize.fn('COUNT', sequelize.col('columnB')), 'labelForCountColumn'],
        ],
        group:["columnA"]
});

How to count a group by query in NodeJS Sequelize

3 Answers3

Linked