How to list products according to a sequence of categories (hierarchical)-CodePudding

I need to return the products according to a sequence of categories (slug column) entered by the user

I was told I should use CTE (RECURSIVE) but I don't have much database experience

Category table

Product table

You can adjust the Where statement to suit your needs.

EDIT

To suit your specific use case, you can use two recursive ctes. The first to filter for male/female and the second to pull in the hierarchal data that you're looking for. You would need to pass the first slug value into the first cte, and the last slug value into the second cte expression:

with recursive first_slug (id, name, parent_category_id) as (
  select     category.id,
             category.name,
             category.parent_category_id
  from       category
  inner join (select id from category where slug = 'male') sq on sq.id = category.id 
  union all
  select     c.id,
             c.name,
             c.parent_category_id
  from       category c
  inner join first_slug
          on c.parent_category_id = first_slug.id
),
last_slug (id, name, parent_category_id) as (
  select     category.id,
             category.name,
             category.parent_category_id
  from       category
  inner join (select id from category where id in (select id from first_slug) and slug = 'pants') sq on sq.id = category.id
  union all
  select     c.id,
             c.name,
             c.parent_category_id
  from       category c
  inner join last_slug
          on c.parent_category_id = last_slug.id
)
  
select * from last_slug;

There could very possibly a cleaner or more efficient way to do this but this is the only way I can think of using only sql.

CodePudding user response：

RESOLVED

1 - Created a 'path' field in category table with the full path of each category ( male/... )

2 - Query:

WITH cte AS (
    SELECT id FROM category WHERE path LIKE 'path%'
)
SELECT p.name, p.category_id, MIN(pd.price) AS price
FROM product p
    INNER JOIN cte
        ON (cte.id = p.category_id)
    INNER JOIN product_detail pd
        ON (pd.product_id = p.id)
WHERE p.active = 'yes' AND pd.stock > 0
GROUP BY p.id