问题描述
我有一个仪器和仪器映射表.一些仪器在仪器映射表中有条目.在映射表中,有真假的状态.单个仪器可以在映射表中以任意随机状态(真/假)重复多次.还有一些仪器在映射表中没有条目.我需要从有效负载主体中获取以下条件
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
- 获取所有工具
{
"instrumentStatus": "",
"searchText": ""
}
- 获取仅在仪器映射表中状态为 true 的所有仪器,即使映射表包含该特定仪器的错误条目.
{
"instrumentStatus": true,
"searchText": ""
}
- 获取除仪器映射表中状态为 true 的仪器之外的所有仪器 -- 这是重要且复杂的仪器.映射表包含单个仪器的许多真假状态.因此,我们需要获取映射表中不存在的所有工具以及映射表中不具有真实状态的工具
- 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": ""
}
仪器表
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 =>
推荐答案
看案例1、2、3,你获取的是
Looking at Case 1, 2, and 3, what you are fetching is
案例1:即使没有instrument_map,您也想获取所有乐器.=>左连接.
Case 1: You want to fetch ALL instruments even if there is no instrument_map. => LEFT JOIN.
案例 2:您只需要 status == true 的 instrument_map.=>内连接
Case 2: You want only instrument_map with a status == true. => INNER JOIN
案例3:即使没有instrument_map,您也想获取所有乐器.=>左加入.但是,排除任何具有 1 个或多个 instrument_map 且 status = true 的工具.
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)
}]
});
这篇关于如何根据sequelize中的关联选择父项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!