0

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.

Ivan Z.
  • 59
  • 1
  • 10
  • 1
    [Please create a fiddle](https://www.db-fiddle.com/) with your table & data example. I'm guessing this is not that hard to do but without any data example, it's a bit hard to imagine. – FanoFN Aug 28 '20 at 08:36
  • Here is a Fiddle example https://www.db-fiddle.com/f/FVC81iWXdZ9cWLEDXcN3X/1 – Ivan Z. Aug 29 '20 at 11:10

1 Answers1

0

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

Ivan Z.
  • 59
  • 1
  • 10