在mysql/php中使用id/parent_id模型获取记录的所有父级的最简单方法是什么? [英] What is the simplest way to get all the parents of a record using the id / parent_id model in mysql/php?

问题描述

我正在寻找使用邻接表/单表继承模型(id, parent_id)从数据库中递归获取所有父元素的最简单方法.

I'm looking for the simplest way to recursively get all the parent elements from a database using the adjacency list / single table inheritance model (id, parent_id).

我的选择当前如下所示:

My select currently looks like this:

$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";

每个event都链接到place，每个place可以是另一个place的子级(最多约5个级别)

Each event is linked to a place, and each place can be a child of another place (upto about 5 levels deep)

使用mysql一次可以选择吗?

Is this possible in a single select with mysql?

此刻，我想这可能是一个单独的函数，该函数循环返回返回的$events数组，并在运行时添加place_parent_X元素，但不确定如何实现.

At the moment I am thinking it could be a separate function which loops through the returned $events array, adding place_parent_X elements as it goes, but am not sure how to implement this.

推荐答案

可以在MySQL中完成，但是您需要创建一个函数并在查询中使用.

It's possible to do it in MySQL, but you'll need to create a function and use in in a query.

有关详细说明，请参阅我的博客中的该条目:

See this entry in my blog for detailed explanations:

• > MySQL中的分层查询

• Hierarchical queries in MySQL

以下是函数和查询:

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

后一个查询将选择给定节点的所有后代(应在@start_with变量中设置)

The latter query will select all descendants of a given node (which you should set in the @start_with variable)

要查找给定节点的所有祖先，可以使用不带函数的简单查询:

To find all ancestors of a given node you can use a simple query without functions:

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

我博客中的这篇文章更详细地描述了此查询:

This article in my blog described this query in more detail:

• 排序列表

要使这两种解决方案都能在合理的时间内工作，您需要在id和parent上都有索引.

For both these solutions to work in reasonable time, you need to have the indexes on both id and parent.

确保将id定义为PRIMARY KEY，并且在parent上具有次要索引.

Make sure your id is defined as a PRIMARY KEY and you have a seconday index on parent.

这篇关于在mysql/php中使用id/parent_id模型获取记录的所有父级的最简单方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！