How to query for multiple records using SQL using 'String Interpolation' that's specific to the 'pg' module Postgres [duplicate]

Question

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

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
– 404, Commented Sep 15, 2021 at 12:06
@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
– MYB, Commented Sep 16, 2021 at 7:57

MYB · Accepted Answer · 2021-09-16 08:00:24Z

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']);

answered Sep 16, 2021 at 8:00

MYB

691 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to query for multiple records using SQL using 'String Interpolation' that's specific to the 'pg' module Postgres [duplicate]

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Related