Quel est le moyen le plus simple d'obtenir tous les parents d'un enregistrement en utilisant le modèle id / parent_id dans mysql/php ?

Question

Je cherche le moyen le plus simple d'obtenir récursivement tous les éléments parents d'une base de données en utilisant la liste d'adjacence / le modèle d'héritage à table unique ( id, parent_id ).

Ma sélection ressemble actuellement à ceci :

$sql = "SELECT
             e.id,
             TIME_FORMAT(e.start_time, '%H:%i') AS start_time,
             $title AS title,
             $description AS description,
             $type AS type,
             $place_name AS place_name,
             p.parent_id AS place_parent_id,
             p.city AS place_city,
             p.country AS place_country
         FROM event AS e
         LEFT JOIN place AS p ON p.id = e.place_id                          
         LEFT JOIN event_type AS et ON et.id = e.event_type_id
         WHERE e.day_id = '$day_id'
         AND e.private_flag = 0
         ORDER BY start_time";

Chaque event est lié à un place et chaque place peut être l'enfant d'un autre place (jusqu'à environ 5 niveaux de profondeur)

Est-ce possible dans une seule sélection avec mysql ?

Pour l'instant, je pense qu'il pourrait s'agir d'une fonction distincte qui bouclerait les données retournées. $events en ajoutant place_parent_X éléments comme il va, mais je ne suis pas sûr de savoir comment mettre cela en œuvre.

Answer 1

Il est possible de le faire en MySQL mais vous devrez créer une fonction et l'utiliser dans une requête.

Voir cet article de mon blog pour des explications détaillées :

• Les requêtes hiérarchiques dans MySQL

Voici la fonction et la requête :

CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _id INT;
        DECLARE _parent INT;
        DECLARE _next INT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id;
        SET _id = -1;

        IF @id IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP
                SELECT  MIN(id)
                INTO    @id
                FROM    place
                WHERE   parent = _parent
                        AND id > _id;
                IF @id IS NOT NULL OR _parent = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @id;
                END IF;
                SET @level := @level - 1;
                SELECT  id, parent
                INTO    _id, _parent
                FROM    place
                WHERE   id = _parent;
        END LOOP;
END

SELECT  id, parent
FROM    (
        SELECT  hierarchy_connect_by_parent_eq_prior_id(id) AS id, @level AS level
        FROM    (
                SELECT  @start_with := 0,
                        @id := @start_with,
                        @level := 0
                ) vars, t_hierarchy
        WHERE   @id IS NOT NULL
        ) ho
JOIN    place hi
ON      hi.id = ho.id

Cette dernière requête sélectionnera tous les descendants d'un nœud donné (que vous devez définir dans le champ @start_with variable)

Pour trouver tous les ancêtres d'un nœud donné, vous pouvez utiliser une simple requête sans fonctions :

SELECT  @r AS _id,
        @r := (
        SELECT  parent
        FROM    place
        WHERE   id = _id
        ) AS parent
FROM    (
        SELECT  @r := @node_id
        ) vars,
        place

Cet article de mon blog décrit cette requête de manière plus détaillée :

• Tri des listes

Pour que ces deux solutions fonctionnent en un temps raisonnable, vous devez avoir les index sur les deux id y parent .

Assurez-vous que votre id est défini comme un PRIMARY KEY et vous avez un indice de second ordre sur parent .

Answer 2

On dirait que le plus simple est ensembles imbriqués .

Answer 3

Ce n'est pas possible avec la conception standard des bases de données parent-enfant.

Cependant, vous pouvez utiliser un ensemble imbriqué et le faire en une seule requête, bien que cela demande un certain travail pour en arriver là.