CODEWITHSUNDEEP
mysqldatabaserecursion
Jonathan Silva
Jonathan Silva

Reputation: 136

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

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

enter image description here

Product table

enter image description here

DB-Fiddle

Examples (male):

slug ('male')

slug ('male', 't-shirts')

slug ('male, 't-shirts', 'tank-top')

slug ('male, 'pants')

MySQL Version: 8.0+

Upvotes: 1

Views: 443

Answers (2)

Jonathan Silva
Jonathan Silva

Reputation: 136

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

Upvotes: 0

Andrew
Andrew

Reputation: 1833

You can use with recursive cte to get the hierarchal data as you mentioned, something like this:

with recursive cte (id, name, parent_category_id) as (
  select     id,
             name,
             parent_category_id
  from       category
  where      parent_category_id = 1 
  union all
  select     c.id,
             c.name,
             c.parent_category_id
  from       category c
  inner join cte
          on c.parent_category_id = cte.id
)
select * from cte;

The query above will get all hierarchical records where the parent_category_id = 1. The results in your case:

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.

Upvotes: 1

Related Questions