so I have product table when I select with raw query if collection not specified then it will get all data, but if the collection is specified it will get per collection. here is my code:

  const { category, collection } = req.query;
  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 cl.id = $2) or (c.c_name = $1)
    order by p."createdAt" desc; `;

   try {
      const getData = await Product.sequelize.query(sql, {
      bind: [category, collection],
   });
   
   if (getData[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,
   });
  }
  };

What I like to achieve is that:

1. When http://localhost:3001/api/v1/product/get?category=man then I will get all data per category either man, woman, or kid
2. When http://localhost:3001/api/v1/product/get?category=man&collection=1 then I will get all data per category either man, woman or kid, but specific collection per id 1=topwear, 2=bottomwear etc.

What it really does when I do above:

1. It only get message: {} when http://localhost:3001/api/v1/product/get?category=man
2. It get all data and doesn't care about the collection when http://localhost:3001/api/v1/product/get?category=man&collection=1

I hope you can understand what I mean and can help me please....

CodePudding user response：

I suppose you need to add a condition to check passed collection value:

 where (c.c_name = $1 and cl.id = $2 and $2 is not null) or (c.c_name = $1 and $2 is null)

And of course you need to pass null in collection variable if the corresponding query parameter in the url is not indicated.

P.S. It's better and more readable to use named parameters in the bind option and SQL query itself, like this:

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 = $category and cl.id = $collection and $collection is not null)
       or (c.c_name = $category and $collection is null)
    order by p."createdAt" desc; `;

   try {
      const getData = await Product.sequelize.query(sql, {
      bind: { category, collection },
   });