CODEWITHSUNDEEP
sqldatabasepostgresqlrecursive-query
user1775888
user1775888

Reputation: 3313

recursive select and join related table

CREATE TABLE IF NOT EXISTS "product_category"(
  "id" SERIAL NOT NULL,
  "parent_id" integer DEFAULT NULL,
  "name" varchar DEFAULT NULL,
  PRIMARY KEY ("id")
);


CREATE TABLE IF NOT EXISTS "product"(
  "id" SERIAL NOT NULL,
  "product_category_id" integer DEFAULT NULL,
  PRIMARY KEY ("id")
);

I have two table like above,
the product_category is hierarchy,
and product.product_category_id fk product_category.id.

How to select all product under specific product_category id,

e.g
if input product_category 1,
output -> product:1, product:2

if input product_category 2
output -> product:2 

product_category
id | parent_id | name
1  |           | parent
2  | 1         | child
3  | 2         | child child


product
id | product_category_id
1  | 1
2  | 3

query

like this ?? but this return only product_category list .... I want product list 

WITH RECURSIVE pc AS (
  SELECT pc.id AS id
    FROM product_category pc

    LEFT JOIN product p ON p.product_category_id = pc.id

    WHERE id = $1 

  UNION ALL

  SELECT child.id
    FROM product_category AS child

    LEFT JOIN product p ON p.product_category_id = child.id

    JOIN pc ON pc.id = child.parent_id 
)
SELECT * FROM product_category WHERE id IN (SELECT * FROM pc)

Upvotes: 1

Views: 1092

Answers (2)

user330315
user330315

Reputation:

You should first build up the list of categories, then join that to the products. 

WITH RECURSIVE pc AS (
  SELECT id
  FROM product_category 
  WHERE id = $id

  UNION ALL

  SELECT child.id
    FROM product_category AS child
    JOIN pc ON pc.id = child.parent_id 
)
SELECT pr.*
FROM product pr
  JOIN pc on pr.product_category_id = pc.id
;

Upvotes: 2

Suraiya Khan
Suraiya Khan

Reputation: 102

Note:I do not have postgresql installed on my system so I am talking based on concepts. I do not see where you have defined the foreign key constraint ... still I am assuming you have done so. And I have not checked the correctness of the CTE/Common Table Expression (- basically with recursive portion of your SQL). Assuming that CTE is correct -

How is about replacing

SELECT * FROM product_category WHERE id IN (SELECT * FROM pc)

with

SELECT * FROM product WHERE product_category_id IN (SELECT * FROM pc)

The correctness of the CTE is the next thing I am going to check.

So apparently the following should work:

;
WITH RECURSIVE pc AS (
    SELECT pc.id AS id
    FROM 
    product_category pc
    LEFT JOIN product p 
    ON p.product_category_id = pc.id
    WHERE pc.id = <Your product category id of interest>
    UNION ALL
    SELECT child.id
    FROM 
    product_category AS child
    LEFT JOIN product p 
    ON p.product_category_id = child.id
    JOIN pc 
    ON pc.id = child.parent_id
)
SELECT id as product_id FROM product WHERE product_category_id 
IN (
   SELECT * FROM pc
);

Upvotes: 1

Related Questions