sequelize: Sequelize count returns wrong count when association models included

My solution, which may break other queries but works for my application, is to add a distinct condition. The distinct param is only applied to the count query so it ensures that the correct document count is returned, whilst allowing the internal Sequelize aggregation of associations to continue as intended.

Adding distinct: 'Article.id' to my Article.findAndCountAll query gives me the correct article count.

Here’s an enhanced workaround that may not cover every case but hasn’t failed me so far:

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

HTH others. 💝

Although I agree this appears to be odd but count and findAll both are returning correct result.

Let’s first talk about findAll, it joins two tables, find 6 rows, sequelize arrange them under attachments so finally you get 3 array items. You can test this by passing raw: true which will return 6 rows.

Similarly count executes same query and see 6 rows, but it don’t need to arrange result into attachments (it can’t as db is returning row count directly) thus returns plain result of 6.

So in your situation I would try these alternatives

1. Won’t use count, I already got findAll result I can use that to show count.
2. Get rid of default scope and use a named scope, only apply that to findAll so count can return result correctly regardless of scope used in findAll

@sushantdhiman I understand why it’s happening but I don’t think this would be the expected behavior for anyone unless they were including { raw: true }.

This is even more troublesome for the folks using findAndCountAll (as in the other issues I’ve linked to). Per the Sequelize docs:

…this is useful when dealing with queries related to pagination where you want to retrieve data with a limit and offset but also need to know the total number of records that match the query.

So, if I used findAndCountAll as the DB in-between for my paginated API query, I could be told there were 6 total records but only have 3 returned. Unfortunately, that does not make any sense from the API consumer standpoint [unless I eventually had 6 finalized records and I was limiting that page to just 3 😉 ].

…although now I’m concerned how the limit and offset options would interact with association-expanded rows like this, too. 😬

I had the same issue and my solution is used distinct: true and col: ${DB.InventoryItem.name}.id` in options of findAndCountAll()

let items = await DB.InventoryItem.findAndCountAll({
        offset: offset,
        limit: limit,
        where: where,
        col: `${DB.InventoryItem.name}.id`,
        distinct: true
})

The root cause must be resolved by remove table left join and left outer join to get match condition based from options when using subfunction this.count(countOptions) in sequelize

adding distinct: true worked for me

@JamesMGreene I 100% agree that the expected behaviour of the count value from findAndCountAll should be the actual number of documents that exist, and not how it is currently implemented.

If I have a document with 2 associations, the count is returning 2 but only 1 document is returned. If I delete one of the associations, then count and returned documents are 1.

I would expect that the count matches the total number of documents, not the total number of documents + total number of associations.

Here’s an enhanced workaround that may not cover every case but hasn’t failed me so far:

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

HTH others. 💝

It’s work

I developed this workaround that addresses the issue for inclusions from options.include:

// 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) {
    throw new Error('A "count" operation executed with scope-based inclusions will return an incorrect result')
  }

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

However, as you can see in the code, it does NOT fix inclusions from the model’s active scope. It could but it would have to actually alter the scope itself, which is not really acceptable as it would negatively impact subsequent use of the scope (or the scoped model, anyway). 😭

The availability of a beforeCountAfterOptions hook (equivalent in concept to the beforeFindAfterOptions hook) would make this achievable but I can see that’s a bit more challenging to implement since this filters down into the aggregate function.