如何在sequelize中根据关联选择父项

Question

我有一个仪器和仪器映射表。有些仪器在仪器映射表中有条目。在映射表中，状态为true或false单个仪器可以在映射表中以任何随机状态(真/假(重复多次。此外，一些仪器在映射表中没有条目我需要从有效载荷主体获取以下条件

1. 获取所有仪器

{
"instrumentStatus": "",
"searchText": ""
}

2. 获取仅在仪器映射表中状态为true的所有仪器，即使映射表包含该特定仪器的false条目

{
"instrumentStatus": true,
"searchText": ""
}

3. 获取所有仪器，不包括状态为true的仪器映射表中的仪器——这是一个重要而复杂的仪器。映射表包含单个仪器的许多真实和错误状态。因此，我们需要获取映射表中不存在的所有仪器以及映射表中没有真实状态的仪器

{
"instrumentStatus": false,
"searchText": ""
}

仪器表

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

仪器映射表

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

根据条件提取仪器的代码

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 =>

Answer 1

查看案例1、2和3，您获取的是

情况1：即使没有instrument_map，也要获取所有仪器=>左加入。

情况2：您只想要状态为==true的instrument_map=>内部联接

情况3：即使没有instrument_map，也要获取所有仪器=>左加入。但是，排除具有一个或多个状态为true的instrument_map的任何仪器。

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

Answer 2

所有情况：require: false

情况1：所有无where条件

案例2:

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

案例3：

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