PostgreSQL : how to query from 3 tables (with 1 junction table)?

table ‘product’

------------------------------------------
| id | product_name | product_description|
------------------------------------------
| 1. | abc          | this is abc's desc |
------------------------------------------

Junction table ‘ingredient_product’

------------------------------------------
| id | product_id   | ingredient_id      |
------------------------------------------
| 1  | 1            | 1                  |
| 1  | 1            | 2                  |
| 1  | 1            | 3                  |
| 1  | 1            | 4                  |
| 1  | 1            | 5                  |
| 1  | 1            | 6                  |
------------------------------------------

table ‘ingredient’

------------------------
| id | ingredient_name |
------------------------
| 1  | apple           | 
| 2  | chicken         |
| 3  | beef            |
| 4  | beet            |
| 5  | oat             |
| 6  | pea fibre       |
------------------------

I have 3 tables and tried to query like below

      SELECT 
             product.name AS product_name, 
             product.description AS product_description, 
             product.created_at AS product_created, 
             ingredient.name AS ingredient_name 
      FROM 
             product 
      JOIN
             ingredient_product 
      ON 
             ingredient_product.product_id = product.id 
      JOIN 
             ingredient 
      ON 
             ingredient.id = ingredient_product.ingredient_id 
      WHERE 
             ingredient_product.product_id = 1;

and I get the result like below

{product_name: "fromm gold", product_description: "For puppies and pregnant or nursing mothers. Taste… aid digestion and salmon oil for a healthy coat.", ingredient_name: "banana"}
{product_name: "fromm gold", product_description: "For puppies and pregnant or nursing mothers. Taste… aid digestion and salmon oil for a healthy coat.", ingredient_name: "strawberry"}
{product_name: "fromm gold", product_description: "For puppies and pregnant or nursing mothers. Taste… aid digestion and salmon oil for a healthy coat.", ingredient_name: "canola oil"}
{product_name: "fromm gold", product_description: "For puppies and pregnant or nursing mothers. Taste… aid digestion and salmon oil for a healthy coat.", ingredient_name: "pilchard"}
{product_name: "fromm gold", product_description: "For puppies and pregnant or nursing mothers. Taste… aid digestion and salmon oil for a healthy coat.", ingredient_name: "ground beef"}
{product_name: "fromm gold", product_description: "For puppies and pregnant or nursing mothers. Taste… aid digestion and salmon oil for a healthy coat.", ingredient_name: "cranberry"}

I get the all different ingredients but wanted to show only once for the duplicates.

is there better way to query this type?

Thank you in advance!

Answer

You seem to want aggregation, something like this:

SELECT p.name AS product_name, p.description AS product_description, 
       p.created_at AS product_created, 
       ARRAY_AGG(i.name) AS ingredient_names
FROM product p JOIN
     ingredient_product ip
     ON ip.product_id = p.id JOIN
     ingredient i
     ON i.id = ip.ingredient_id 
WHERE p.id = 1
GROUP BY p.id;

Notes:

Table aliases make the query easier to write and read.
The WHERE clause filters on the primary key of product rather than on the equivalent column in ingredient_product. I think that primary keys may help the optimizer.
This adds a GROUP BY, because you want one row per product. This is aggregating by the primary key, so the SELECT can contain other columns.
The array_agg() brings the ingredients together as an array.

Advertisement

Answer