sequelize: findAndCountAll count is wrong when query with associations

Hi guys, Unfortunately I stumbled upon a very unpleasant bug, that I cannot understand nor figure out how to work out. It’s about when query with associations, the count is different from the returned values. Here’s an example (quite simplified though):

Question.belongsToMany(models.Resource, {
    through: models.QuestionToTag,
    as: 'Tags'
});

Resource.belongsToMany(models.Question, {
     through: models.QuestionToTag,
    as: 'Tags'
});

orm.models.Question.findAndCountAll({
    include: [
        model: Resource,
        as: 'Tags',
        where: {
            'id': { $in: [15, 16] }
        }
    ]
}).then(function(result) {
    console.log(result);
});

I have 3 questions, few different Resources (tags), and this is the result: Sequelize findAndCountAll association error

You can see that I’ve made a few relations, and that question #1 is associated to three resources (two of which I query - #15 and #16) - #15, #16 and #17. On the bottom half you can see the result from the debugger, stating that there are is a total of 4 items (count = 4), but the result contains only 3 rows. Question with id 1 is listed only once.

This is the count query (styled a little):

SELECT
    count(`Question`.`id`) AS `count` 
FROM
    `Questions` AS `Question`
INNER JOIN 
    (`question_to_tags` AS `Tags.QuestionToTag`
        INNER JOIN
            `Resources` AS `Tags`
            ON 
            `Tags`.`id` = `Tags.QuestionToTag`.`resource_id`
    )
    ON
        `Question`.`id` = `Tags.QuestionToTag`.`question_id`
    AND
        `Tags`.`id` IN (15, 16);

This is a big problem for me as I’m dealing with pagination and I need to have the total amount of items, so that I can make proper offset/limit query.

Any ideas?

About this issue

Original URL
State: closed
Created 9 years ago
Reactions: 10
Comments: 28 (9 by maintainers)

Commits related to this issue

adding distinct:true to list sequelize findAndCountAll options to deal with bug https://github.com/sequelize/sequelize/issues/4042 — committed to bcolthurst/finale by bcolthurst 6 years ago
Getting shallow data instead of nested data (#36) * Allowing a context variable called 'shallow' to disable includes in list operations, to keep things shallow when desired, and to bypass standard ea... — committed to tommybananas/finale by bcolthurst 5 years ago

Most upvoted comments

After brute force tries, I figured out findAndCountAll({ distinct: true ...}) fixes the problem. It does something similar to what you’ve posted (maybe I’ve messed up some brackets somewhere)… It would be very nice if you update the docs somehow about this issue…

Thanks!

+103

fourpixels on Jul 1, 2015

I am finding a distinct(*) problem; I had the includes inside of a scoped model before the findAndCountAll options. I found a fix which is not explicitly mentioned in this thread so I’ll bring it up (not IDEAL, but it works for now:

I had to pass into the query options include:[] in addition distinct: true;

Even though my sequelize query already had includes from a scope. Feels like a work around, but works just fine. 😃

+12

alockwood05 on Mar 2, 2017

Hey all, sorry to bring up an old issue, but I was experiencing the same situation. I am using MySQL, and findAndCountAll was returning a number higher than appropriate when associations were including via the include option.

I saw @janmeier said it is fixed in commit 97d355a, but I do not see the code in master. Adding the code to my project fixed the problem, however, I am concerned that the code never made it/didn’t stick around in master.

Did this code introduce a bug?

Rudedog9d on Jan 15, 2018

Sorry if my explanation was too technical 😃

A required include is an include which results in an inner join. This can happen either if where is present on the include, or if explicitly set by required: true.

orm.models.Question.findAndCountAll({
    include: [
        model: Resource,
        as: 'Tags',
        where: {
            'id': { $in: [15, 16] }
        }
   ]
});

The include for tags is required, because where is set. The question must match a tag with one of the following ids.

orm.models.Question.findAndCountAll({
    include: [
        model: Resource,
        as: 'Tags',
        where: {
            'id': { $in: [15, 16] }
        },
       required: false
   ]
});

This include is explicitly marked as not required, and will return all questions, but only those tags with id 15 or 16.

For the count part of findAndCount we only keep the required includes - because the left join ones don’t have any impact on the count.

If a count query has includes we add count(id) as opposed to count(*). So count(DISTINCT(*)) will never happen, because we only set distinct: true from findAndCount when we pass includes to the count part

janmeier on Jul 1, 2015

SELECT count(DISTINCT `Question`.`id`) AS `count`

janmeier on Jul 1, 2015

I have this same problem with 6.5.0

Using findAndCountAll or findAll and count, in both cases the count number does not match the total provided objects

EDIT: Using distinct: true solves the issue

cccaballero on Oct 18, 2021

I’m sure what we’re arguing here? If there are required includes (include with where) we will set distinct: true And if there are includes passed to count, it will include the primary key in the query, resulting in COUNT(DISTINCT id). Are you saying there are cases where this breaks for you?

janmeier on Jul 1, 2015

Yes, we already take that into account - If you look at the code I referenced you’ll se that it only adds the distinct if there are includes on the count query

janmeier on Jul 1, 2015

As I said, this has already been fixed in master 😃 https://github.com/sequelize/sequelize/commit/97d355aa80cad217a8bd7190a6c00433a73f367f

janmeier on Jul 1, 2015

i have similar issue then i try to add findAndCountAll({ subQuery: false …}) and its fix my problem.

cmiwjaeger on Aug 18, 2020

The fix I posted adds distinct in the code - I just wanted you to test if adding distinct to the query you posted gives the right result if you run the query manually?

janmeier on Jul 1, 2015