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,
    };
});

Answer 1

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

Answer 2

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