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