在 NodeJS 中将多个数据库查询优化为一个

Question

我的 Node (app.js( 文件中有下面的"事件"和"产品数据"对象。如果product_id匹配，我正在循环并进行三次数据库调用。

const incident = {
primary_product_type: 'T',
product_id: 29,
components: 
[ { component_id: 2,
name: 'wheel',
is_public: false,
is_vehicle: false,
is_equipment: false,
is_tire: false,
is_child_seat: false,
is_active: false,
is_deleted: false},
{ component_id: 3,
name: 'brake',
is_public: false,
is_vehicle: false,
is_equipment: false,
is_tire: false,
is_child_seat: false,
is_active: false,
is_deleted: false } ]
}
const productData = { tires: [{
tire_id: 1,
product_id: 29, 
complaint_id: 1,
failure_type_cd: 'BST',
failure_location_cd: 'PSR',
original_equipment_cd: 'REPL',
repair_type_cd: 'RSW',
repair_location_cd: 'BE',
},
{
tire_id: 2,
product_id: 2,
complaint_id: 1,
failure_type_cd: 'BST',
failure_location_cd: 'PSR',
original_equipment_cd: 'REPL',
repair_type_cd: 'RSW',
repair_location_cd: 'BE'
}]};        

在这里，我通过传递代码类型进行循环并进行了三个不同的调用。

for (const x of productData.tires) {
if (x.product_id === incident.product_id) {
const getTireOeDescription = await productsDB.getProductDescriptionByCode(context, x.original_equipment_cd);
const getTireFlDescription = await productsDB.getProductDescriptionByCode(context, x.failure_location_cd);
const getTireFtDescription = await productsDB.getProductDescriptionByCode(context, x.failure_type_cd);
}
}

在 productDB 文件中，我有一个简单的 postgres sql 来获取基于传递参数的代码的描述。

const descriptionByCodeSql = ` 
SELECT
description
FROM ivoq.product_codes
WHERE code = $1 and
is_deleted != true`;
const getProductDescriptionByCode = async (context, code) =>
db.selectOne(context.dbClient, descriptionByCodeSql, [code]);

这里的问题是它会调用它 3 次。我正在尝试找到一种方法来只调用此sql一次。

Answer 1

使用IN

const descriptionByCodeSql = ` 
SELECT code, description
FROM ivoq.product_codes
WHERE code IN ($1, $2, $3) and
is_deleted != true`;
const getProductDescriptionByCode = async (context, codes) =>
db.select(context.dbClient, descriptionByCodeSql, codes);