Как вывести всех родителей у подкатегории до главной категории?

Question

[Yura_Mart]

Добрый день.
Имеются категории и подкатегории, вложенность может быть до 4 уровня подкатегории.
Необходимо отобразить на странице подкатегории хлебные крошки, чтобы по нажатию на любого предка переходить на его страницу.
Известен id текущей подкатегории $subcategory_id = 4, и по ней необходимо узнать только её предков, вплоть до родительской у которой parent_id = 0.
Как устроена БД:


Как я думаю:
- надо сформировать массив со всеми предшествующими подкатегориями и основной категории
- вывести этот массив в таком виде, т.е. от главного родителя до текущей подкатегории


Что сделано:
Попытался сделать функцию для формирования массива, но выдаёт ошибку
"Uncaught TypeError: Cannot access offset of type string on string" на строке " $sub_array['text'] = $row['title'];"

Как я думаю, мне нужно положить в массив "название" и "id" каждой подкатегории и основной категории.
Далее использовать рекурсию, чтобы обработать любое кол-во вложенности.

function get_node_data($subcategory_id)
{
    global $connection;
    $query = "SELECT * FROM category WHERE id = $subcategory_id";
    $res = mysqli_query($connection, $query);
    $result = mysqli_fetch_assoc($res);
            $output = array();
            foreach ($result as $row) {
                $sub_array = array();
                $sub_array['title'] = $row['title'];
                $sub_array['id'] = $row['id'];
                $sub_array['nodes'] = array_values(get_node_data($row['parent_id']));
                $output[] = $sub_array;
            }
            return $output;
}

Вот часть кода, куда необходимо всё выводить

<ul class="breadcrumb">
        <li class="breadcrumb-item"><a href="<?= PATH ?>">Главная</a></li>
       <li class="breadcrumb-item">Категория</li>
 </ul>

Собственно просьба помочь разобраться и подправить код, что делаю не так или не правильно мыслю?

Comments

[Akina]

Вот что за привычка - притащить весь массив на PHP и потом его долго и нудно жевать кривым кодом? когда задача элементарно решается одним простым запросом...

Точная версия MySQL - какая?

[FanatPHP]

ну и зачем здесь рекурсия?

предлагаю написать получение списка родителей вручную, по очереди, и посмотреть, что получилось
дальше станет ясно, как делать

[Yura_Mart]

Akina, 8.0.24

[Akina]

Yura_Mart, в этой версии рекурсивные CTE доступны - см. запросы в ответах.

[Дмитрий]

Akina, можно и процедуры писать, 4 запроса проще в понимании

[Akina]

Дмитрий,

4 запроса проще в понимании

Очевидно, что для того, кто в PHP как рыба в воде, а в SQL - очень приблизительно, это аргумент. Но для того, кто в этих областях хотя бы поровну (и при этом не на уровне "ниже плинтуса", само собой), эта фраза скорее всего покажется ... ну как минимум не сильно близкой к истине.

Говорят, каждый должен заниматься своим делом. Так эффективнее. Если следовать этому принципу, то обработкой данных должен заниматься специально предназначенный для этого сервер баз данных. А попытка сделать ту же операцию, но в программе на клиенте - это заведомо и очевидно менее эффективное решение. Ибо с одной стороны - результат труда кучи программистов-профессионалов, с мощными тестированием, отладкой, тщательным вылизыванием каждого момента работы, с другой - одного программиста, да ещё может и подпёртого дедлайном... и один программист сделает лучше? ну-ну...

Да, есть классы задач обработки данных, для которых сервера БД не предназначены. Но текущая - вот явно не из их числа.

Answer 1

[Akina]

WITH RECURSIVE
cte AS ( SELECT *, 1 level
         FROM category 
         WHERE id = $category_id
         UNION ALL
         SELECT cat.*, cte.level + 1
         FROM category cat
         JOIN cte ON cat.id = cte.parent_id )
SELECT *
FROM cte
ORDER BY level;

Для древних версий:

SELECT CONCAT_WS('=>', c1.id, c2.id, c3.id, c4.id, c5.id) path
FROM category c1
LEFT JOIN category c2 ON c1.parent_id = c2.id
LEFT JOIN category c3 ON c2.parent_id = c3.id
LEFT JOIN category c4 ON c3.parent_id = c4.id
LEFT JOIN category c5 ON c4.parent_id = c5.id
WHERE c1.id = $category_id

Ну соответственно подрихтовать до нужного вида выходного набора.

Comments

[FanatPHP]

каких только извращений люди не придумают, лишь бы не делать 4 простых запроса :)

и это. мне кажется, что path - это немного другое

[Akina]

FanatPHP, Мои запросы - это просто демонстрация того, что всё может быть уложено в один запрос. Который, кстати, намного эффективнее, чем 4 последовательных запроса - особенно если MySQL находится на другом хосте. Да и на PHP-коде какая-то экономия.

[Yura_Mart]

Akina, Спасибо за решение!
Вариант для Mysql 8 сработал, правда он труднее в понимании, чем 4 простых запроса, но это уже мои проблемы. Надо учить SQL. И да, такой способ более компактный и удобный, на мой взгляд.

[FanatPHP]

Yura_Mart, если вы не понимаете как написать простейший цикл, то вам надо сначала учить основы программирования, а не "учить" SQL в значении "записать в блокнотике крутой запрос не имея ни малейшего представления, как он работает"

Answer 2

[Rsa97]

В MySQL 8 решается одним запросом:

WITH RECURSIVE `cte` (`id`, `parent_id`, `title`, `n`) AS (
  SELECT `id`, `parent_id`, `title`, 0
    FROM `table`
    WHERE `id` = :categoryId
  UNION
  SELECT `t`.`id`, `t`.`parent_id`, `t`.`title`, `n`+1
    FROM `cte`
    JOIN `table` AS `t` ON `t`.`id` = `cte`.`parent_id`
)
SELECT `id`, `title`
  FROM `cte`
  ORDER BY `n` DESC

Остаётся только выбрать все строки из ответа и вывести их в цикле.

Comments

[Yura_Mart]

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