Comment créer une requête récursive hiérarchique MySQL

pour les versions MySql qui ne supportent pas les Expressions courantes de Table (jusqu'à la version 5.7), vous obtiendriez ceci avec la requête suivante:

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))

voici un violon .

la valeur spécifiée dans @pv := '19' doit être définie à la id du parent que vous voulez sélectionner tous les descendants de.

cela fonctionnera aussi si un parent a multiple enfant. Toutefois , il est nécessaire que chaque enregistrement remplisse la condition parent_id < id , sinon les résultats ne seront pas complets.

cette requête utilise une syntaxe MySql spécifique: les variables sont assignées et modifiées lors de son exécution. Certaines hypothèses sont formulées au sujet de l'ordre d'exécution:

• la clause from est évaluée en premier. C'est donc là que @pv obtient initialisé.
• Le where la clause est évaluée pour chaque enregistrement dans l'ordre de récupération des alias from . C'est donc là qu'une condition est posée pour inclure seulement les enregistrements pour lesquels le parent était déjà identifié comme étant dans l'arbre descendant (tous les descendants du parent primaire sont progressivement ajoutés à @pv ).
• les conditions de cette clause where sont évaluées dans l'ordre, et l'évaluation est interrompue une fois que le résultat total est certain. Par conséquent, la deuxième condition doit être à la deuxième place, car il ajoute le id à la liste de parent, et cela ne devrait se produire que si le id passe la première condition. La fonction length n'est appelée que pour s'assurer que cette condition est toujours vraie, même si la chaîne pv donnerait pour une raison quelconque une valeur fausse.

en somme, on peut trouver ces hypothèses trop risquées pour s'y fier -- il n'y a pas de garantie documentée pour elles, et même si elle fonctionne toujours, l'ordre d'évaluation peut, en théorie, encore changer lorsque vous utilisez cette requête comme une vue ou d'une sous-requête dans un plus grand requête.

notez Aussi que pour les très grands ensembles de données, cette solution peut devenir lent, comme la find_in_set opération n'est pas la meilleure façon de rechercher un numéro dans une liste, certainement pas dans une liste qui atteint une taille du même ordre de grandeur que le nombre d'enregistrements renvoyés.

variante 1: WITH RECURSIVE , CONNECT BY

de plus en plus de bases de données mettent en œuvre la SQL:1999 ISO standard WITH [RECURSIVE] syntaxe pour les requêtes récursives (par exemple Postgres 8.4+ , SQL Server 2005+ , DB2 , Oracle 11gR2+ , SQLite 3.8.4+ , Firebird 2.1+ , H2 , HyperSQL 2.1.0+ , Teradata , MariaDB 10.2.2+ ). Et à partir de version 8.0, MySql supporte aussi . Avec cette syntaxe, la requête ressemble à ceci:

with recursive cte (id, name, parent_id) as
(
 select     id,
            name,
            parent_id
 from       products
 where      parent_id = 19
 union all
 select     p.id,
            p.name,
            p.parent_id
 from       products p
 inner join cte
         on p.parent_id = cte.id
)
select * from cte;

certaines bases de données ont une syntaxe alternative non standard pour les recherches hiérarchiques, comme la clause CONNECT BY disponible sur les bases de données Oracle. DB2 supporte également cette syntaxe alternative.

MySql version 5.7 ne propose pas une telle fonctionnalité. Lorsque votre moteur de base de données fournit cette syntaxe, alors c'est certainement la meilleure option à utiliser. Si non, alors aussi envisager les solutions suivantes.

Alternative 2: Chemin-style Identifiers

les choses deviennent beaucoup plus faciles si vous assignez des valeurs id qui contiennent les information: un chemin d'accès. Par exemple, dans votre cas, cela pourrait ressembler à ceci:

ID       | NAME
19       | category1   
19/1     | category2  
19/1/1   | category3  
19/1/1/1 | category4  

puis votre select ressemblerait à ceci:

select  id,
        name 
from    products
where   id like '19/%'

Alternative 3: Self-joined

si vous connaissez une limite supérieure pour la profondeur de votre arbre de hiérarchie peut devenir, vous pouvez utiliser un standard sql comme ceci:

select      p6.parent_id as parent6_id,
            p5.parent_id as parent5_id,
            p4.parent_id as parent4_id,
            p3.parent_id as parent3_id,
            p2.parent_id as parent2_id,
            p1.parent_id as parent_id,
            p1.id as product_id,
            p1.name
from        products p1
left join   products p2 on p2.id = p1.parent_id 
left join   products p3 on p3.id = p2.parent_id 
left join   products p4 on p4.id = p3.parent_id  
left join   products p5 on p5.id = p4.parent_id  
left join   products p6 on p6.id = p5.parent_id
where       19 in (p1.parent_id, 
                   p2.parent_id, 
                   p3.parent_id, 
                   p4.parent_id, 
                   p5.parent_id, 
                   p6.parent_id) 
order       by 1, 2, 3, 4, 5, 6, 7;

voir ce violon

la condition where spécifie le parent dont vous voulez récupérer les descendants. Vous pouvez étendre cette requête avec plus de niveaux que nécessaire.

le blog De la Gestion Hiérarchique des Données dans MySQL

structure de la Table

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

Requête:

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

sortie

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+

la plupart des utilisateurs à un moment ou un autre ont traité des données hiérarchiques dans une base de données SQL et sans doute appris que la gestion de données hiérarchiques n'est pas ce qu'une base de données relationnelle est prévu pour. Les tables d'une base de données relationnelle ne sont pas hiérarchiques (comme XML), mais sont simplement une liste plate. Les données hiérarchiques ont une relation parent-enfant qui n'est pas naturellement représentée dans une table de base de données relationnelles. lire la suite

Consulter le blog pour plus de détails.

EDIT:

select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where parent=@pv

sortie:

category_id name    parent
19  category1   0
20  category2   19
21  category3   20
22  category4   21

Référence: How pour faire la requête de sélection récursive dans Mysql?

définition de la Table:

DROP TABLE IF EXISTS category;
CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    parent_id INT,
    CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
    REFERENCES category (id)
) engine=innodb;

lignes expérimentales:

INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);

Récursif de la procédure Stockée:

DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
    DECLARE catname VARCHAR(20);
    DECLARE temppath TEXT;
    DECLARE tempparent INT;
    SET max_sp_recursion_depth = 255;
    SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
    IF tempparent IS NULL
    THEN
        SET path = catname;
    ELSE
        CALL getpath(tempparent, temppath);
        SET path = CONCAT(temppath, '/', catname);
    END IF;
END$$
DELIMITER ;

fonction D'enrubannage pour la procédure stockée:

DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE res TEXT;
    CALL getpath(cat_id, res);
    RETURN res;
END$$
DELIMITER ;

choisir l'exemple:

SELECT id, name, getpath(id) AS path FROM category;

sortie:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1                               |
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA                     |
| 24 | categoryB | category1/categoryA/categoryB           |
| 25 | categoryC | category1/categoryA/categoryC           |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+

lignes de filtrage avec certains chemins:

SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';

sortie:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
+----+-----------+-----------------------------------------+

La meilleure approche que j'ai trouvé est

1. utiliser la lignée pour stocker les arbres\sort\trace. C'est plus que suffisant, et travaille des milliers de fois plus vite pour la lecture que toute autre approche. Il permet également de rester sur ce modèle même si DB va changer (comme n'importe quel db permettra que ce modèle soit utilisé)
2. utiliser la fonction qui détermine la lignée pour ID spécifique.
3. utilisez - le comme vous le souhaitez (dans les sélections, ou sur les opérations de la CUD, ou même par des emplois).

de la Lignée de l'approche descr. peut être trouvé partout, par exemple Ici ou ici . Comme fonction - que est ce qui m'a enspiré.

à la fin-obtenu plus ou moins simple, relativement rapide, et solution SIMPLE.

corps de fonction

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_lineage`(the_id INT) RETURNS text CHARSET utf8
    READS SQL DATA
BEGIN

 DECLARE v_rec INT DEFAULT 0;

 DECLARE done INT DEFAULT FALSE;
 DECLARE v_res text DEFAULT '';
 DECLARE v_papa int;
 DECLARE v_papa_papa int DEFAULT -1;
 DECLARE csr CURSOR FOR 
  select _id,parent_id -- @n:=@n+1 as rownum,T1.* 
  from 
    (SELECT @r AS _id,
        (SELECT @r := table_parent_id FROM table WHERE table_id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := the_id, @l := 0,@n:=0) vars,
        table m
    WHERE @r <> 0
    ) T1
    where T1.parent_id is not null
 ORDER BY T1.lvl DESC;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open csr;
    read_loop: LOOP
    fetch csr into v_papa,v_papa_papa;
        SET v_rec = v_rec+1;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- add first
        IF v_rec = 1 THEN
            SET v_res = v_papa_papa;
        END IF;
        SET v_res = CONCAT(v_res,'-',v_papa);
    END LOOP;
    close csr;
    return v_res;
END

et puis vous juste

select get_lineage(the_id)

Espère que cela aide quelqu'un :)

a fait la même chose pour une autre question ici

Mysql sélectionnez get récursive tous les enfants avec plusieurs niveau

la requête sera:

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(id SEPARATOR ',') FROM table WHERE parent_id IN (@pv)) AS lv FROM table 
JOIN
(SELECT @pv:=1)tmp
WHERE parent_id IN (@pv)) a;

si vous avez besoin de vitesse de lecture rapide, la meilleure option est d'utiliser une table de fermeture. Une table de fermeture contient une rangée pour chaque couple ancêtre/descendant. Donc dans votre exemple, la table de fermeture ressemblerait à

ancestor | descendant | depth
0        | 0          | 0
0        | 19         | 1
0        | 20         | 2
0        | 21         | 3
0        | 22         | 4
19       | 19         | 0
19       | 20         | 1
19       | 21         | 3
19       | 22         | 4
20       | 20         | 0
20       | 21         | 1
20       | 22         | 2
21       | 21         | 0
21       | 22         | 1
22       | 22         | 0

une fois que vous avez cette table, les requêtes hiérarchiques deviennent très faciles et rapides. Pour obtenir tous les descendants de la catégorie 20:

SELECT cat.* FROM categories_closure AS cl
INNER JOIN categories AS cat ON cat.id = cl.descendant
WHERE cl.ancestor = 20 AND cl.depth > 0

bien sûr, il y a un gros inconvénient chaque fois que vous utilisez des données dénormalisées comme celle-ci. Vous avez besoin pour maintenir la table de fermeture à côté de votre table de catégories. La meilleure façon est probablement d'utiliser les déclencheurs, mais il est quelque peu complexe de suivre correctement les inserts/mises à jour/suppressions pour les tables de fermeture. Comme pour tout, vous devez examiner vos exigences et décider quelle approche est la meilleure pour vous.

Edit : voir la question quelles sont les options pour stocker des données hiérarchiques dans une base de données relationnelle? pour plus d'options. Y sont différentes solutions optimales pour différentes situations.

vous pouvez le faire comme ceci dans d'autres bases de données assez facilement avec une requête récursive (YMMV sur la performance).

l'autre façon de le faire est de stocker deux bits supplémentaires de données, une valeur gauche et droite. Les valeurs de gauche et de droite sont dérivées d'une traversée avant ordre de la structure de l'arbre que vous représentez.

c'est connu comme Préorder modifié Tree Traversal et vous permet d'exécuter une requête simple pour obtenir toutes les valeurs parent à la fois. Il va aussi par le le nom de "nested set".

simple requête pour lister les enfants de première récursion:

select @pv:=id as id, name, parent_id
from products
join (select @pv:=19)tmp
where parent_id=@pv

résultat:

id  name        parent_id
20  category2   19
21  category3   20
22  category4   21
26  category24  22

... à gauche:

select
    @pv:=p1.id as id
  , p2.name as parent_name
  , p1.name name
  , p1.parent_id
from products p1
join (select @pv:=19)tmp
left join products p2 on p2.id=p1.parent_id -- optional join to get parent name
where p1.parent_id=@pv

la solution de @teincot pour lister tous les enfants:

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv) > 0
and     @pv := concat(@pv, ',', id)

tester en ligne avec SQL Fiddle et voir tous les résultats.

http://sqlfiddle.com/#!9 / a318e3 / 4 / 0

il est un peu délicate, il faut vérifier ce que c'est de travailler pour vous

select a.id,if(a.parent = 0,@varw:=concat(a.id,','),@varw:=concat(a.id,',',@varw)) as list from (select * from recursivejoin order by if(parent=0,id,parent) asc) a left join recursivejoin b on (a.id = b.parent),(select @varw:='') as c  having list like '%19,%';

SQL fiddle link http://www.sqlfiddle.com/#!2 / e3cdf/2

remplacer par le nom de votre zone et de votre table.

il suffit d'utiliser BlueM/arbre classe php pour faire l'arbre de l'auto-rapport de la table dans mysql.

Tree et Tree\Node sont des classes PHP pour traiter des données qui sont structurées hiérarchiquement en utilisant des références d'ID parent. Un exemple typique est une table dans une base de données relationnelle où le champ "parent" de chaque enregistrement renvoie à la clé primaire d'un autre enregistrement. Bien sûr, Tree ne peut pas seulement utiliser des données provenant d'une base de données, mais n'importe quoi: vous fournir les données et Tree les utilise, peu importe d'où proviennent les données et comment elles ont été traitées. lire la suite

voici un exemple d'utilisation de BlueM/tree:

<?php 
require '/path/to/vendor/autoload.php'; $db = new PDO(...); // Set up your database connection 
$stm = $db->query('SELECT id, parent, title FROM tablename ORDER BY title'); 
$records = $stm->fetchAll(PDO::FETCH_ASSOC); 
$tree = new BlueM\Tree($records); 
...

Je l'ai trouvé plus facilement à:

1) créer une fonction qui vérifie si un élément n'importe où dans la hiérarchie parent d'une autre. Quelque chose comme ceci (je n'écrirai pas la fonction, je la ferai avec WHILE DO):

is_related(id, parent_id);

dans votre exemple

is_related(21, 19) == 1;
is_related(20, 19) == 1;
is_related(21, 18) == 0;

2) Utilisez une sous-sélection , quelque chose comme ceci:

select ...
from table t
join table pt on pt.id in (select i.id from table i where is_related(t.id,i.id));

quelque chose qui n'est pas mentionné ici, bien qu'un peu similaire à la deuxième alternative de la réponse acceptée, mais différent et à faible coût pour la grande question de hiérarchie et facile (mise à jour supprimer) articles, serait d'ajouter une colonne de chemin persistant pour chaque élément.

certains aiment:

id | name        | path
19 | category1   | /19
20 | category2   | /19/20
21 | category3   | /19/20/21
22 | category4   | /19/20/21/22

exemple:

-- get children of category3:
SELECT * FROM my_table WHERE path LIKE '/19/20/21%'
-- Reparent an item:
UPDATE my_table SET path = REPLACE(path, '/19/20', '/15/16') WHERE path LIKE '/19/20/%'

optimiser la longueur du chemin et ORDER BY path en utilisant l'encodage base36 à la place numérique chemin id

 // base10 => base36
 '1' => '1',
 '10' => 'A',
 '100' => '2S',
 '1000' => 'RS',
 '10000' => '7PS',
 '100000' => '255S',
 '1000000' => 'LFLS',
 '1000000000' => 'GJDGXS',
 '1000000000000' => 'CRE66I9S'

https://en.wikipedia.org/wiki/Base36

Supprimer également le séparateur slash ' / 'en utilisant la longueur fixe et le rembourrage à l'id encodé

explication détaillée de l'optimisation ici: https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path /

TODO

construction d'une fonction ou d'une procédure pour diviser le chemin pour les ancêtres d'un objet

j'ai fait une requête pour vous. Cela vous donnera la catégorie récursive avec une seule requête:

SELECT id,NAME,'' AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 WHERE prent is NULL
UNION 
SELECT b.id,a.name,b.name AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id WHERE a.prent is NULL AND b.name IS NOT NULL 
UNION 
SELECT c.id,a.name,b.name AS subName,c.name AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id WHERE a.prent is NULL AND c.name IS NOT NULL 
UNION 
SELECT d.id,a.name,b.name AS subName,c.name AS subsubName,d.name AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id LEFT JOIN Table1 AS d ON d.prent=c.id WHERE a.prent is NULL AND d.name IS NOT NULL 
ORDER BY NAME,subName,subsubName,subsubsubName

voici un violon .