SQLinfo.ru - Все о MySQL

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 20.11.2023 22:34:28

OlegR
Участник
Зарегистрирован: 14.06.2022
Сообщений: 21

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

Есть простая таблица `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` узлов, для которых нужно получить пути к корню?
На выходе нужно получить выборку с путями до корня для всех узлов из второй таблицы.

Спасибо!

Неактивен

 

#2 22.11.2023 14:26:54

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5828

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

Путь до корня это различный по количеству набор строк для разных узлов. Неясно в каком виде вы хотите получить результат.
У вас в запросе 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)

Неактивен

 

#3 22.11.2023 22:22:38

OlegR
Участник
Зарегистрирован: 14.06.2022
Сообщений: 21

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

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

Неактивен

 

#4 23.11.2023 03:15:06

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5828

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

Имхо, никак.

Неактивен

 

#5 23.11.2023 19:48:09

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5828

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

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

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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson