Postgres pg模块如何用字符串插值实现任意数量品牌的SQL多记录查询

方式1：使用ANY操作符（更简洁，推荐） #

pg模块支持直接将数组作为单个参数传递，配合PostgreSQL的ANY运算符可以完美适配任意长度的品牌数组查询，不需要动态拼接占位符：

const getByBrands = async (brands = []) => {
  // 空数组直接返回空结果，避免无效查询
  if (!brands.length) return [];
  const review = await pool.query(`
    SELECT b.brand "Brand", c.country "country",
      r.id "ID", r.stars "Stars",
      s.packaging_style "Style",
      r.url "URL", r.variety "Variety"
    FROM brands b
    JOIN reviews r ON r.brand_id = b.id
    -- 请替换为你表实际的关联字段，原有SQL缺失这两个关联条件会产生笛卡尔积
    JOIN countries c ON c.id = r.country_id 
    JOIN packaging_styles s ON s.id = r.packaging_style_id
    WHERE b.brand = ANY($1)
  `, [brands]);
  return review.rows;
};
// 调用方式无需修改
getByBrands(['Koka', 'Boss', 'Peyang']);

方式2：动态生成占位符（兼容IN语法的实现） #

如果需要保留IN的写法，可以根据输入数组的长度动态生成对应数量的占位符：

const getByBrands = async (brands = []) => {
  if (!brands.length) return [];
  // 生成 $1,$2,...$n 格式的占位符串
  const placeholders = Array.from({ length: brands.length }, (_, idx) => `$${idx + 1}`).join(',');
  const review = await pool.query(`
    SELECT b.brand "Brand", c.country "country",
      r.id "ID", r.stars "Stars",
      s.packaging_style "Style",
      r.url "URL", r.variety "Variety"
    FROM brands b
    JOIN reviews r ON r.brand_id = b.id
    JOIN countries c ON c.id = r.country_id
    JOIN packaging_styles s ON s.id = r.packaging_style_id
    WHERE b.brand IN (${placeholders})
  `, brands);
  return review.rows;
};

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