How to select parent item based on the association in sequelize

Solution 1:

Looking at Case 1, 2, and 3, what you are fetching is

Case 1: You want to fetch ALL instruments even if there is no instrument_map. => LEFT JOIN.

Case 2: You want only instrument_map with a status == true. => INNER JOIN

Case 3: You want to fetch ALL instruments even if there is no instrument_map. => LEFT JOIN. However, exclude any instrument which has 1 or more instrument_map with status = true.

// Make sure req.body.status is either true or false. If it is not neither true nor false, status becomes undefined.
const status = [true, false].includes(req.body.status) ? req.body.status : undefined;

// Case 1: no condition.
let statusCond;

// Case 3: Get instrument whose instrument_map does NOT EXISTS with status = true.
if (status === false) {
    statusCond = Sequelize.literal(`NOT EXISTS ( \
        SELECT 1 FROM instrument_map \
        WHERE instrument.id = instrument_map.instrumentId \
            AND status = true)`)

// Case 2: Filter to status = true only
} else if (status === true) {
    statusCond = { '$InsturmentMap.status$': true }
}

db.Instrument.findAndCountAll({
    where: {
        [Op.or]: [
            Sequelize.where(Sequelize.fn('lower', Sequelize.col('name')), Op.like, `%${body.searchText}%`),
            Sequelize.where(Sequelize.fn('lower', Sequelize.col('description')), Op.like, `%${body.searchText}%`),
        ],
        [Op.and]: statusCond
    },
    distinct: true,
    include: [{
        model: db.InstrumentMap,
        as: 'InstrumentMap',
        attributes: ['status'],
        required: status || false,  // Only if req.body.status is true, we need INNER JOIN. Otherwise, LEFT JOIN (required: false)
    }]
});

Solution 2:

All cases: require: false

Case 1: no where condition at all

Case 2:

where: {
  '$"InstrumentMap".status$': true
}

Case 3:

where: {
   [Op.or]: [
   {
    '$"InstrumentMap".id$': null
   },
   {
    '$"InstrumentMap".status$': false
   }
   ]
}