0

I have four tables: brands, countries, packaging_styles, reviews

The requirement is: Write a function to find reviews for a set of brands.

NOTE1: The brands param is an array of strings - Array of brands.
NOTE2: The function should return an array of review object that match the given brands.

The query itself is tested in Postico application and it works as it should be

const getByBrands = async (brands = []) => {
  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, countries c, reviews r, packaging_styles s\
  WHERE r.brand_id = b.id\
  AND b.brand IN ($1, $2, $3)', brands);
  return review.rows;
};
getByBrands(['Koka', 'Boss', 'Peyang']);

Now what I am stuck at is I can only get records for three brands as opposed to as much as I want

MYB
  • 69
  • 8
  • You need to use `ANY` instead of `IN` to compare to an array. I don't know exactly how it would be in javascript but in postgres/SQL it should end up looking like this: `AND b.brand = ANY(['Koka', 'Boss', 'Peyang'])`. Ideally you should be able to mark the array param as `ANY($1)` and have the array be placed in there, so it will work with an array of any size. – 404 Sep 15 '21 at 12:06
  • I think my issue is more of js than sql. – MYB Sep 15 '21 at 13:36
  • @404. the link helped indeed and it fixed the issue. Thanks a lot. Please have a look of how I did it and confirm. This may be useful for someone else later. – MYB Sep 16 '21 at 07:57

1 Answers1

0

This is how I fixed the problem

const getByBrands = async (brands = []) => {
  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, countries c, reviews r, packaging_styles s\
  WHERE r.brand_id = b.id\
  AND b.brand = ANY ($1::text[])', [brands]);
  console.log(review.rows);
  return review.rows;
};
getByBrands(['Koka','Boss']);
MYB
  • 69
  • 8