Sequelize get count of association based on where condition

I have a table Patient which is associated to another table Visits. A Patient can have many visits and one Visit is associated with one Patient.Visit is connected to a Patient via patient_uuid foreign key​Visit.belongsTo(Patient, {as: 'patient',foreignKey: 'patient_uuid'});​Patient.hasMany(Visit, {foreignKey: 'patient_uuid'});​Visit also has a visit_status field ('SCHEDULED', 'COMPLETED', 'CANCELLED').When I get list of all Patients I want to show visit count where the status is COMPLETED.I tried doing this:​Patient.findAll({attributes: ['uuid', 'first_name', 'last_name', 'mobile_phone',[Sequelize.fn("COUNT", Sequelize.col("visits.uuid")), "visitCount"]],include: [{model: Visit,where: {visit_status: 'COMPLETED'},attributes: []}],order: [['first_name', 'ASC']]});I do get patients list and it has the visitCount values, however the query completely skips those patients where the visit status is not completed.I would like to get all the patients with visitCount and for those where the status is not COMPLETED I would like to see 0. But it drops those patient records from the result.Is there any way to include the patient records where the visitCount is 0?

Submitted May 28, 2019 at 04:10PM by codeinprogress