Sequelize count or findAndCountAll return invalid result

53 views Asked by At

Trying to count rows in the parent table while adding include -> where statement. Both count() and findAndCountAll() return invalid results.

For the patient id 7h95Aj1P I have exactly one treatment in the database while total treatments is 49

With findAndCountAll

const countResultItems = await this.model.findAndCountAll({
    distinct: true,
    subQuery: false,
    col: 'Treatment.id',
    include: [
        {
            model: DiagnosisSequelizeModel,
            required: true,
            where: {
                patient_id: '7h95Aj1P'
            }
        }
    ]
});
console.log('Count from xxx function', countResultItems.count);

which returns me Count from countResultItems function 49

Raw sql is correct since while executing this directly I get 1 result

SELECT
    `Treatment`.`id`,
    `Treatment`.`diagnosis_id`,
    `Treatment`.`physician_id`,
    `Treatment`.`medication_id`,
    `Treatment`.`country_id`,
    `Treatment`.`status`,
    `Treatment`.`start_date`,
    `Treatment`.`end_date`,
    `Treatment`.`end_reason`,
    `Treatment`.`created_at`,
    `Treatment`.`updated_at`,
    `Treatment`.`deleted_at`,
    `diagnosis`.`id` AS `diagnosis.id`,
    `diagnosis`.`patient_id` AS `diagnosis.patient_id`,
    `diagnosis`.`disease_id` AS `diagnosis.disease_id`,
    `diagnosis`.`physician_id` AS `diagnosis.physician_id`,
    `diagnosis`.`date` AS `diagnosis.date`,
    `diagnosis`.`created_at` AS `diagnosis.created_at`,
    `diagnosis`.`updated_at` AS `diagnosis.updated_at`,
    `diagnosis`.`deleted_at` AS `diagnosis.deleted_at`
FROM
    `treatment` AS `Treatment`
INNER JOIN
    `diagnosis` AS `diagnosis`
ON
    `Treatment`.`diagnosis_id` = `diagnosis`.`id`
AND
    (`diagnosis`.`deleted_at` IS NULL AND `diagnosis`.`patient_id` = '7h95Aj1P')
WHERE
    (`Treatment`.`deleted_at` IS NULL);

With count()

const countResultItems = await this.model.count({
    distinct: true,
    col: 'Treatment.id',
    include: [
        {
            model: DiagnosisSequelizeModel,
            required: true,
            where: {
                patient_id: '7h95Aj1P'
            }
        }
    ]
});
console.log('Count from xxx function', countResultItems);

returns me again returns me Count from countResultItems function 49

and the created sql statement is missing the join

SELECT
    COUNT(DISTINCT(`Treatment`.`id`)) AS `count`
FROM
    `treatment` AS `Treatment`
WHERE
    (`Treatment`.`deleted_at` IS NULL);

I know there are a lot of issues around this problem but it seems weird that basic count function doesn't even produce the join clause.

Is there a way to fix it?

Sequelize version ^6.32.1

0

There are 0 answers