sequelize: Sequelize findAndCountAll returns wrong count when association models included

What are you doing?

I have Post model. Post model has attachment(oneToMany),users(oneToMany), I’m trying to fetch posts with attachments using sequelize findAndCountAll

// POST MODEL
module.exports = (sequelize, DataTypes) => {
     const Post = sequelize.define(
        'Post',
        {
            id: {
                primaryKey: true,
                type: DataTypes.UUID
            },
            title: DataTypes.STRING(50)
        },
        {
            tableName: 'post',
            timestamps: true
        }
    );

    Post.associate = models => {
        Post.hasMany(models.PostAttachment, {
            as: 'attachment',
            foreignKey: 'postId'
        });
    };

    return Post;
};

// POST ATTACHMENT
module.exports = (sequelize, DataTypes) => {
    const PostAttachment = sequelize.define(
        'PostAttachment',
        {
            id: {
                primaryKey: true,
                type: DataTypes.UUID
            },
            key: DataTypes.STRING
        },
        {
            tableName: 'postAttachment',
            timestamps: true
        }
    );

    PostAttachment.associate = models => {
        PostAttachment.belongsTo(models.Feed, {
            as: 'post',
            foreignKey: 'postId'
        });
    };

    return PostAttachment;
};

POST TABLE

id	title	createdAt	updatedAt
f8d68fc0-48b1-4e90-af73-c9a4dc577461	Title1	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00
01a528d2-8696-465f-8739-2b32bc52ceca	Title2	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00

POST ATTACHMENT TABLE

id	key	postId	createdAt	updatedAt
410819f6-d1c4-44f4-9a52-edbac765e714	Key1	f8d68fc0-48b1-4e90-af73-c9a4dc577461	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00
754ca095-4967-4472-a0f4-1a8e2d761a24	Key2	f8d68fc0-48b1-4e90-af73-c9a4dc577461	2018-05-23 16:47:09.228000 +00:00	2018-05-23 16:47:09.228000 +00:00

SEQUELIZE QUERY

const posts = Post.findAndCountAll({
    include: ['attachment', 'users', 'x count of models']
});

 return {
    "posts": posts.rows,
    "total": posts.count
};

What do you expect to happen?

I’m expecting get 2 posts and total sum 2

{
"posts": [{...},{...}],
"total": 2
}

What is actually happening?

I’m getting 2 posts and total sum 4

{
"posts": [{...},{...}],
"total": 4
}

Dialect: postgres Dialect version: XXX Database version: PostgreSQL 10.3 on x86_64-apple-darwin17.3.0, compiled by Apple LLVM version 9.0.0 (clang-900.0.39.2), 64-bit Sequelize version: ^4.37.8

About this issue

Original URL
State: open
Created 6 years ago
Reactions: 40
Comments: 19 (1 by maintainers)

Commits related to this issue

fix bug that was not returning correct messages count this bug is apparently due to sequelize findAndCountAll method which adds extra count in cases where there are other models included (in this cas... — committed to KenzieAcademy/kwitter-api by thurt 5 years ago
Fix for https://github.com/sequelize/sequelize/issues/9481 — committed to supercrytoking/Devcash-Bounty-Platform by bbedward 4 years ago

Most upvoted comments

@tumdav try adding distinct:true to the findAndCountAll passed object that should do the trick, i had the same problem few days ago and that solve it

const posts = Post.findAndCountAll({
    include: ['attachment', 'users', 'x count of models'],
    distinct:true
});

+167

ErnestoSalazar on May 31, 2018

Give this workaround a try:

// Add a permanent global hook to prevent unknowingly hitting this Sequelize bug:
//   https://github.com/sequelize/sequelize/issues/10557
sequelize.addHook('beforeCount', function (options) {
  if (this._scope.include && this._scope.include.length > 0) {
    options.distinct = true
    options.col = this._scope.col || options.col || `"${this.options.name.singular}".id`
  }

  if (options.include && options.include.length > 0) {
    options.include = null
  }
})

+16

JamesMGreene on Nov 4, 2019

I’ve the same issue adding distinct:true did not solve the problem for me. I’m using MSSQL dialect

+13

kshateesh on Jul 3, 2018

the issue seems to be fixed, adding distinct: true works

+11

ibadnabihashmi on Dec 3, 2021

The complex your queries get, the workaround stops working. For example, If I want to pass where argument in the include. For example

const data = User.findAndCountAll({ include: [{as: 'order', model: Order, where: { orderName: 'laptop' } }] })

Now the count will be wrong with the workaround by @JamesMGreene (which was genius, tho). This needs to be properly fixed

talhabalaj on Jul 20, 2021

I’ve run into this issue numerous times, and today encountered it again. 3+ years and it hasn’t been fixed? There is obviously a workaround available posted by @JamesMGreene , thanks for that, but it shouldn’t be required for such a core piece of functionality of Sequelize to work correctly. This example is even in the official docs and it doesn’t even work!

mikemerritt on Jun 20, 2021

distinct: true works but remember, your query must have an @Index column

faraz9x on Jan 24, 2022

Give this workaround a try:

// Add a permanent global hook to prevent unknowingly hitting this Sequelize bug:
//   https://github.com/sequelize/sequelize/issues/10557
sequelize.addHook('beforeCount', function (options) {
  if (this._scope.include && this._scope.include.length > 0) {
    options.distinct = true
    options.col = this._scope.col || options.col || `"${this.options.name.singular}".id`
  }

  if (options.include && options.include.length > 0) {
    options.include = null
  }
})

I added this hook inside models/index.js and it works 👍

//Fix the wrong count issue in findAndCountAll()
sequelize.addHook('beforeCount', function (options) {
  if (this._scope.include && this._scope.include.length > 0) {
    options.distinct = true
    options.col = this._scope.col || options.col || `"${this.options.name.singular}".id`
  }

  if (options.include && options.include.length > 0) {
    options.include = null
  }
})
db.sequelize = sequelize;
db.Sequelize = Sequelize;

db.users = require("./user.js")(sequelize, Sequelize);

shahzadthathal on Dec 18, 2020

The bug is introduced in this PR to fix another issue. Currently the findAndCountAll method does not work as documented because the documentation is of the old implementation.

The previous version of this issue was fixed and closed a while ago.

raviqqe on Oct 29, 2019

dyaskur

Try to used separate: true in association if you count is wrong when you used findAndCountAll with associate

bhojakRahul13 on Nov 24, 2022

Thank you! distinct: true was the answer I was looking for.

ilanl on Jul 12, 2022