Форум пользователей MySQL

OlegR · 20.11.2023 22:34:28

Есть простая таблица `users` с двумя полями - `id` и `parent_id`.
Запрос получения пути от какого либо узла до корня дерева:

SET @node = 30268;

SELECT @node, u2.id, level

FROM (SELECT @r AS _id, 

      (SELECT @r := parent_id 

       FROM `users` 

       WHERE id = _id) AS parent_id, 

      @l := @l + 1 AS level 

      FROM (SELECT @r := @node, @l := 0) AS init, `users` AS u 

      WHERE @r <> 0) u1 

JOIN `users` AS u2 ON u1._id = u2.id 

ORDER BY level LIMIT 1

Здесь константа 30268 - `id` узла, от которого получаем путь к корню.
Никак не соображу - как вместо этой константы подставлять значения из другой таблицы, которая содержит `id` узлов, для которых нужно получить пути к корню?
На выходе нужно получить выборку с путями до корня для всех узлов из второй таблицы.

Спасибо!

vasya · 22.11.2023 14:26:54

Путь до корня это различный по количеству набор строк для разных узлов. Неясно в каком виде вы хотите получить результат.
У вас в запросе limit 1, т.е. вы всегда будете получать только значение заданного узла и level = 1.

Пользовательские переменные - прошлый век. По феншую - обобщенные выражения.
Кроме того они позволяют одним запросом посчитать путь для нескольких узлов.
На примере из https://sqlinfo.ru/articles/info/28.html

CREATE TABLE category(

        category_id INT AUTO_INCREMENT PRIMARY KEY,

        name VARCHAR(20) NOT NULL,

        parent INT DEFAULT NULL

);

INSERT INTO category VALUES(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);

-- определим путь для корня для узлов с category_id равными 5 и 8

mysql> WITH RECURSIVE cte AS

    -> (

    ->   SELECT name, parent, CAST(category_id AS CHAR(200)) AS path FROM category WHERE category_id in (5,8)

    ->   UNION ALL

    ->   SELECT c.name, c.parent, CONCAT(cte.path, ",", c.category_id) FROM category c JOIN cte

    ->   ON c.category_id=cte.parent

    -> )

    -> SELECT path FROM cte where parent is null;

+---------+

| path    |

+---------+

| 5,2,1   |

| 8,7,6,1 |

+---------+

2 rows in set (0.00 sec)

OlegR · 22.11.2023 22:22:38

Ну, к сожалению, на сервере стоит версия 5.7!
Запрос я опубликовал не полный - там просто среди родительских узлов ищется первый ближайший, который удовлетворяет некоторому фильтру. Поэтому использую LIMIT 1.
Работает кстати быстро и точно.
Сейчас это работает в связке с php-циклом по списку целевых узлов.
Хотел все объединить в один запрос.
Поэтому и спрашиваю - как соединить этот код с запросом на выборку целевых узлов из другой таблицы.

vasya · 23.11.2023 03:15:06

Имхо, никак.

vasya · 23.11.2023 19:48:09

К вопросу о быстродействии.
Сейчас переменные вычисляются столько раз сколько строк в таблице `users`. Если в `users` много ветвей, то, как минимум, можно вместо `users` использовать меньшую последовательность (главное, чтобы она была больше, чем максимально длинная ветвь).

В 5.7 можно имитировать обобщенные табличные выражения с помощью хранимой процедуры, см https://sqlinfo.ru/articles/info/22.html
Тогда на вход можно будет передавать набор значений.
Возможно получится сразу искать только первый ближайший родительский узел, который удовлетворяет некоторому фильтру.

Форум пользователей MySQL

#1 20.11.2023 22:34:28

Путешествие по дереву

#2 22.11.2023 14:26:54

Re: Путешествие по дереву

#3 22.11.2023 22:22:38

Re: Путешествие по дереву

#4 23.11.2023 03:15:06

Re: Путешествие по дереву

#5 23.11.2023 19:48:09

Re: Путешествие по дереву

Board footer