Sequelize PostgreSQL原生查询按category、collection动态筛选问题

问题根因 #

• 1. WHERE条件逻辑错误：原逻辑(c.c_name = $1 and cl.id = $2) or (c.c_name = $1)等价于c.c_name = $1，不管collection参数是否存在、值是什么，都只会按分类筛选，完全不会触发系列筛选，这是双参数时筛选不生效的核心原因。
• 2. 空值绑定异常：未传collection参数时，req.query.collection为undefined，Sequelize绑定undefined到SQL时会转为NULL，cl.id = NULL在PostgreSQL中永远返回假，同时原有空结果判断逻辑错误（getData[0] != ""不适用于数组判空，JS中空数组[] == ""返回true，会误判查询结果）。
• 3. 参数类型不匹配：collection是系列ID的数字类型，直接绑定字符串格式的query参数会导致匹配失败。

修复后代码 #

const { category, collection } = req.query;
// 处理空值和类型转换，未传collection时主动设为null
const bindParams = [
  category,
  collection ? Number(collection) : null
];
const sql = `
  select 
   p.id,
   p.p_image, 
   p.p_name, 
   p.p_desc,
   p.p_prize, 
   p.p_stock,
   c.c_name,
   cl.cl_name
  from products as p
  inner join collections as cl on cl.id = p.p_collection_id 
  inner join categories as c on c.id = cl.cl_category_id
  -- 修正后的条件：必选分类匹配，可选系列匹配
  where c.c_name = $1 
    and ($2::int is null or cl.id = $2)
  order by p."createdAt" desc; `;

 try {
    const getData = await Product.sequelize.query(sql, {
      bind: bindParams,
    });
    // 修正数组判空逻辑，用length判断
    if (getData[0].length > 0) {
        res.status(200).send({
          s: 1,
          message: "success retrive all products",
          data: getData[0],
        });
     } else {
        res.status(404).send({
          s: 0,
          message: "data not found",
       });
      }
} catch (err) {
  res.status(500).send({
    // 输出错误信息方便排查
    message: err.message || "server error"
 });
}
};

验证效果 #

• 仅传category参数时，$2值为null，$2::int is null条件成立，仅按分类筛选商品，符合需求。
• 同时传category和collection参数时，$2为数字类型的系列ID，cl.id = $2条件生效，仅返回指定分类下对应系列的商品，符合需求。

内容的提问来源于stack exchange，提问作者Muhammad Haekal