findAndCountAll count gets a bigger number than the actual returned data

Question

I am using Sequelize findAndCountAll() for my website's pagination.

The findAndCountAll() returns the total items number(3990) is bigger than the actual returned data number (3770).

Does anyone know how to make the findAndCountAll() returns the total items number which is the same as the actual data returned?

The table which I use findAndCountAll() queries including two other tables, like the code below:

const patientModel: IIncludeOptions = {
    model: Patient,
    attributes: ["deviceId", "firstName", "lastName"],
};

const deviceModel: IIncludeOptions = {
    model: Device,
    required: true,
    attributes: ["deviceId", "deviceSerialNumber"],
    include: [patientModel],
};

const eventCodeModel: IIncludeOptions = {
    model: EventCode,
    required: true,
    attributes: ["name"],
};

const opts: IFindOptions = {
    ...pageSizeLimit,
    offset : offsetNum,
    attributes: ["id", "deviceId", "eventId", "dispatchedAt", "message", "createdAt"],
    include: [
        deviceModel,
        eventCodeModel,
    ],
    order: sortBy,
};

const resCount = await DeviceEvent.findAndCountAll(opts).then((response) => {
    const totalItems = response.count;
    return {
        totalItems,
        currentPage: page || 1,
    };
});

How are you counting "the actual returned data number (3770)" ? — Emma, Oct 14 '20 at 19:50
@Emma findAndCountAll() method returns an array which contains all the data being fetched from database, the array's length reflects the actual returned data number. — JavaScript Rookie, Oct 16 '20 at 16:06
If I read correctly, you need `distinct: true` in `opts`, this will count the number of `DeviceEvent` itself (array length). `findAndCountAll()` counts the # of records that is fetched which means if you have `include`, it will count the number of records with joined objects. — Emma, Oct 16 '20 at 16:17

score 5 · Answer 1 · answered Jan 06 '23 at 05:41

What worked for me is adding distinct: true to the query. Without this Sequelize returns the count for the values without an inner join/required: true.

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

In the code used in the question, this would be:

const opts: IFindOptions = {
    ...pageSizeLimit,
    offset : offsetNum,
    attributes: ["id", "deviceId", "eventId", "dispatchedAt", "message", "createdAt"],
    include: [
        deviceModel,
        eventCodeModel,
    ],
    order: sortBy,
    distinct: true,
};

Details for this are mentioned here - Count issue in findandCountAll

Works. You rock. This was driving me nuts. Thanks. – KManish Aug 23 '23 at 15:02 — KManish, Aug 23 '23 at 15:02

score 0 · Answer 2 · answered Oct 14 '20 at 19:08

Try to check your SQL logging the query.

Follow those instructions to get the query dump: How can I see the SQL generated by Sequelize.js?

And see if the count number is the same that findAndCountAll() returns to you.

Maybe the way that sequelize.js mounts the query affects the results.

score 0 · Answer 3 · answered Jul 26 '23 at 11:32

0

You have to set separate: true in your option when you have one-to-many associations, for example :

users.findAndCountAll({
include:[{model:messages,as:"messages",separate:true}]
})

answered Jul 26 '23 at 11:32

mohammad tavassolian

71
6

findAndCountAll count gets a bigger number than the actual returned data

3 Answers3