CODEWITHSUNDEEP
sequelize.js
KIRAN K J
KIRAN K J

Reputation: 732

How to select parent item based on the association in sequelize

I am having an instrument and instrument mapping table. Some of the instruments have entries in the instrument mapping table. In the mapping table, there is the status with true or false. A single instrument can be repeated multiple times in the mapping table with any random status(true/false). Also some Instruments don't have entry in the mapping table. I need to fetch below conditions from payload body

  1. Fetch all instruments
{
   "instrumentStatus": "",
   "searchText": ""
}
  1. Fetch all instruments that are only in the instrument mapping table with status as true even if the mapping table contains false entry for that particular instrument.
{
   "instrumentStatus": true,
   "searchText": ""
}
  1. Fetch all instruments excluding those which are in the instrument mapping table with status true -- This is the important and complicated one. The mapping table contains many true and false status with a single instrument. So we need to fetch all instruments that are not present in the mapping table as well as instruments that are not having true status in the mapping table
{
   "instrumentStatus": false,
   "searchText": ""
}

Instrument table

module.exports = (sequelize, DataTypes) => {
    const Instrument = sequelize.define("instrument", {
        id: { type: INTEGER, primaryKey: true, autoIncrement: true },
        name: { type: STRING },
        description: { type: STRING }
    }, {
        timestamps: false,
        freezeTableName: true,
    })
    Instrument.associate = function (models) {
        Instrument.hasMany(models.instrument_map, { as: "InstrumentMap" });
    };
    return Instrument;

}

Instrument mapping table

module.exports = (sequelize, DataTypes) => {
    const InstrumentMap = sequelize.define("instrument_map", {
        id: { type: INTEGER, primaryKey: true, autoIncrement: true },
        instrumentId: { type: INTEGER },
        status: { type: BOOLEAN, defaultValue: 1 },

    }, {
        timestamps: false,
        freezeTableName: true,
    })
    InstrumentMap.associate = function (models) {
        InstrumentMap.belongsTo(models.instrument, { as: "instrument", foreignKey: 'instrumentId' });
    };
    return InstrumentMap;

}

Code to fetch instruments based on the condition

let condition = [];

if (body.instrumentStatus != null && body.instrumentStatus != "" && body.instrumentStatus) {
    condition.push(Sequelize.where(Sequelize.count("InstrumentMap." + "status"), Sequelize.Op.eq, body.instrumentStatus));
}

db.instrument.findAndCountAll({
            where: {
                    [Sequelize.Op.or]: [
                        Sequelize.where(Sequelize.fn('lower', Sequelize.col("instrument." + "name")), Sequelize.Op.like, '%' + body.searchText + '%'),
                        Sequelize.where(Sequelize.fn('lower', Sequelize.col("instrument." + "description")), Sequelize.Op.like, '%' + body.searchText + '%')
                    ],

                    [Sequelize.Op.and]: condition
            },
            order: [
                [Sequelize.fn('lower', Sequelize.col("instrument.name" )), "ASC"]
            ],
            offset: body offset,
            limit: lbody.imit,
            distinct: true,
            subQuery: false,
            include: [
            {
                    model: db.instrument_map,
                    as: 'InstrumentMap',
                    attributes: ['status'],
                    required: true,
                }
            ]
            }).then(result =>

Upvotes: 1

Views: 266

Answers (2)

Emma
Emma

Reputation: 9308

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

Upvotes: 1

Anatoly
Anatoly

Reputation: 22783

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

Upvotes: 1

Related Questions