How to get data from MySQL hierarchical category tree using Adjacency List Model?

Question

I am building a MySQL relational database with the InnoDB engine for a shopping application as an exercise (I am a noob, so sorry in advance for beginner question).

For product categories, I have decided to use the Adjacency List Model for a hierarchical data tree. There are two tables of concern:

• Category

1. category_id PK
2. name
3. parent_id

• Product

1. id PK
2. name
3. desc
4. price
5. category_id FK

I have found a query from Mike Hillyer to retrieve full tree:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.parent = t1.category_id
LEFT JOIN categories AS t3 ON t3.parent = t2.category_id
LEFT JOIN categories AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

but I can't figure out how to connect it with the products table.

The query I used referencing this question to get a childs of specific category (19th element):

SELECT  category_id,
        name,
        parent_id 
FROM    (select * from categories
         order by parent_id, category_id) products_sorted,
        (select @pv := '19') initialisation
WHERE   find_in_set(parent_id, @pv)
AND     length(@pv := concat(@pv, ',', category_id))

I supose I should use JOIN to select from both tables, but aparently I am not getting the right result.

What I want to do, is to get products by category_id, but also to retrieve parent categories.

Also when the user clicks on the root or any level of child categories I want to get all products from child nodes also.

Answer 1

Solution for getting all products from the given category (with subcategories) is

SELECT *
FROM    products
WHERE   category_id IN
(
    SELECT category_id
    FROM `categories`
    WHERE FIND_IN_SET(`category_id`, (
    SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
      SELECT @Ids := (
          SELECT GROUP_CONCAT(`category_id` SEPARATOR ',')
          FROM `categories`
          WHERE FIND_IN_SET(`parent_id`, @Ids)
      ) Level
      FROM `categories`
      JOIN (SELECT @Ids := 1) temp1
   ) temp2
))
)

The query above selects all products from category (and child categories) with an ID of 1

And here is Fiddle link