Передать значения из Select в CROSS JOIN CTE в MySQL

У меня есть таблица в базе данных MySQL, а именно "пользователи"

CREATE TABLE `users` (
  `user_id` int NOT NULL,
  `name` varchar(100) NOT NULL,
  `parent_user_id` int
);

INSERT INTO `users` (`user_id`, `name`, `parent_user_id`) VALUES
(1, 'John', null), 
(2, 'Emma', 1), 
(3, 'Watson', 2),
(4, 'Peter', 3), 
(5, 'Rose', 1), 
(6, 'Harry', 4),
(7, 'Jim', 6), 
(8, 'Jack', 5), 
(9, 'Josh', 8),
(10, 'Jem', 9);

Мне нужно запросить таблицу users, которая возглавляет большинство предков, а не пользователя root (т.е. пользователем root является 1)

Ожидаемый результат:

user_id     name          root_parent_user_id     root_parent_user_name
______________________________________________________________
1           John          null               null
2           Emma          1                  John
3           Watson        2                  Emma
4           Peter         2                  Emma
5           Rose          1                  John
6           Harry         2                  Emma
7           Jim           2                  Emma
8           Jack          5                  Rose
9           Josh          5                  Rose
10          Jem           5                  Rose

Логика требования:

Дело 1 — ЕСЛИ родительский идентификатор равен NULL:

  • У пользователя есть parent_id null, поэтому это самый первый пользователь, и у него нет родителя/предка.

Дело №2: - ЕСЛИ Parent Id равен 1 (что означает, что пользователь был создан самым первым предком)

  • У пользователя parent_id 1, поэтому нам нужно взять родителя 1

Дело №3: - ЕСЛИ родительский идентификатор равен N (что означает, что пользователь был создан N-м дочерним элементом случая № 2)

  • У пользователя есть parent_id n (например: user_id равен 6, а n равен 4), поэтому нам нужно найти корневого родителя, который был создан самым первым предком, и, наконец, ответ «user_id: 2» (т.е. Эмма) .

Я попробовал следующий код:

SELECT * FROM users USR
CROSS JOIN (
WITH RECURSIVE cte (user_id, name, parent_user_id) AS
    (
      SELECT user_id, name, parent_user_id
        FROM users
        WHERE user_id = USR.user_id
      UNION ALL
      SELECT c.user_id, c.name, c.parent_user_id
        FROM cte AS cp JOIN users AS c
          ON cp.parent_user_id = c.user_id
    )
    SELECT * FROM cte where parent_user_id = 1
) PUSR ON 1 = 1;

Я через ошибку Код ошибки: 1054. Неизвестный столбец «USR.user_id» в «предложении where»

Пожалуйста, помогите мне, как передать значение внутри CTE, где условие из приведенного выше запроса и как добиться запрошенного вывода.

Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
0
0
290
1

Ответы 1

Я не думаю, что это лучший подход, так как это будет стоить вам больших затрат на обработку и может стать кошмаром для дальнейшего обслуживания вашего приложения. Вы должны просто сохранить пользователя root для каждого пользователя в базе данных (это то, как большинство древовидных систем справляются с этим). Ваша структура таблицы станет: идентификатор_пользователя | имя | parent_user_id | root_user_id

Если вы действительно хотите продолжать в том же духе, я бы посоветовал вам проверить это (вам просто нужно добавить к нему условие «не пользователь 1 корень»): https://dba.stackexchange.com/questions/7147/найти-наивысший-уровень-из-иерархического-поля-с-vs-без-ctes/7150

Другие вопросы по теме