MySQL hierarchical recursive update

Question

I need to update a hierarchical structure in MySql. It's about categories in a shop.

Table:

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sort` int(11) NOT NULL,
  `level` int(11) NOT NULL,
  `visible` tinyint(1) NOT NULL DEFAULT '1',
  `product_count` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `sort` (`sort`),
  KEY `visible` (`visible`)
);

+------+------------------------------+---------------+-------+------+
| id   | name                         | product_count | level | sort |
+------+------------------------------+---------------+-------+------+
|    1 | Cat1                         |             0 |     0 |    0 |
|    2 | Cat1.1                       |             0 |     1 |    1 |
|    3 | Cat1.1.1                     |            82 |     2 |    2 |
|    4 | Cat2                         |             4 |     0 |    3 |
|    5 | Cat3                         |             0 |     0 |    4 |
|    6 | Cat3.1                       |             0 |     1 |    5 |
|    7 | Cat3.2                       |             0 |     1 |    6 |
|    8 | Cat3.2.1                     |            12 |     2 |    7 |
|    9 | Cat3.2.1.1                   |             0 |     3 |    8 |

The hierarchical structure is create with the fields "sort" and "level". I couldn't change this because it's not "my" table-design.

I need an UPDATE which set the field "visible" to true or false according to the rule: "A categorie is visible if it contains products OR if it is a parent of a categorie that contains products".

So in my example the result should be:

+------+------------------------------+---------+
| id   | name                         | visible |
+------+------------------------------+---------+
|    1 | Cat1                         |       1 |
|    2 | Cat1.1                       |       1 |
|    3 | Cat1.1.1                     |       1 |
|    4 | Cat2                         |       1 |
|    5 | Cat3                         |       1 |
|    6 | Cat3.1                       |       0 |
|    7 | Cat3.2                       |       1 |
|    8 | Cat3.2.1                     |       1 |
|    9 | Cat3.2.1.1                   |       0 |

Is it possible to solve this with one query in MySql 5.6?

Besides you should provide a better acsii (text formatted) data table because it mismatches the `create table` statement.. Like column order and used colums. — Raymond Nijland, Jul 09 '18 at 13:33
Might help https://stackoverflow.com/questions/20215744/… one way to do a recursive query in MySQL versions under MySQL 8.0.. Or https://stackoverflow.com/questions/3752078/recursive-stored-functions-in-mysql — Raymond Nijland, Jul 09 '18 at 13:37

score 0 · Answer 1 · answered Jul 09 '18 at 14:18

0

SELECT c1.id, c1.name, SUM(c2.product_count) > 0 AS visible
FROM categories AS c1
JOIN categories AS c2 ON c2.name LIKE CONCAT(c1.name, '%')
GROUP BY c1.id

answered Jul 09 '18 at 14:18

Bill Karwin

538,548
86
673
828

MySQL hierarchical recursive update

1 Answers1