Как оптимально получить материнские элементы из базы?

Например есть таблица
id
pid
name

есть оптимальный способ получить все элементы одной ветки, одним sql запросом?
например для Breadcrumb
В голову приходит одно решение запросить все элементы и записать $arr[pid]

Дополнительно:

Из вопроса непонятные, что вы понимаете под всеми элементами одной ветки. Приведите пример.

Ответы:

Не указано, что за сервер SQL используется. Вот, например, для MySQL 8:

WITH RECURSIVE `cte` AS (   SELECT `id`, `pid`, `name`, 0 AS `level`     FROM `table`   UNION SELECT `p`.`id`, `p`.`pid`, `p`,`name`, `cte`.`level` + 1 AS `level`     FROM `cte`      JOIN `table` AS `p` ON `p`.`id` = `cte`.`pid` ) SELECT `id`, `name`, `level`   FROM `cte`   ORDER BY `level`

• Решение интересное, мне кажется, это ресурсозатратно. Спасибо! буду пробовать
• Элдор Суванов, Несколько запросов будут гораздо затратнее.
• Элдор Суванов,
  

  мне кажется, это ресурсозатратно.

  Только если у вас миллионы категорий, на значениях порядка нескольких тысяч категорий это пыль...

На php можно так попробовать, но тут не один запрос

$query = "SELECT id, pid, name FROM your_table"; $result = $mysqli->query($query);  $items = []; while($row = $result->fetch_assoc()){     $items[$row['id']] = ['pid' => $row['pid'], 'name' => $row['name']]; }  function buildTree(array $elements, $parentId = 0) {     $branch = array();      foreach ($elements as $element) {         if ($element['pid'] == $parentId) {             $children = buildTree($elements, $element['id']);             if ($children) {                 $element['children'] = $children;             }             $branch[] = $element;         }     }      return $branch; }  $tree = buildTree($items);

• function pids($sql, $sid) { $sql = json_decode(json_encode($sql), true); $index = $sid; $arr = []; foreach ($sql as $n) foreach ($sql as $item) { list($id, $pid) = array_values($item); if ($id == $index) { $index = $pid; $arr[] = $item; } } return $arr; } $breadcrumb[] = ['name' => 'Главная', 'href' => '/']; $breadcrumb[] = ['name' => 'Категории', 'href' => route('admin.categories')]; $categories = DB::table('categories') -> select('id') -> addSelect('categories_id') -> addSelect('categories_name') -> get(); $tmp = array_reverse(pids($categories, intval($product -> categories_id))); foreach ($tmp as $item) $breadcrumb[] = ['name' => $item['categories_name'], 'href' => route('admin.categories.list', ['id' => $item['id']])];

  function pids($sql, $sid) { $sql = json_decode(json_encode($sql), true); $index = $sid; $arr = []; foreach ($sql as $n) foreach ($sql as $item) { list($id, $pid) = array_values($item); if ($id == $index) { $index = $pid; $arr[] = $item; } } return $arr; } $breadcrumb[] = ['name' => 'Главная', 'href' => '/']; $breadcrumb[] = ['name' => 'Категории', 'href' => route('admin.categories')]; $categories = DB::table('categories') -> select('id') -> addSelect('categories_id') -> addSelect('categories_name') -> get(); $tmp = array_reverse(pids($categories, intval($product -> categories_id))); foreach ($tmp as $item) $breadcrumb[] = ['name' => $item['categories_name'], 'href' => route('admin.categories.list', ['id' => $item['id']])];

  Я реализовал проше, просто хочу найти решение где не придется, получать всю таблицу из базы и не делать лишние итерации или это не нужная оптимизация?

• Элдор Суванов, Можно попробовать сделать через with recursive в базе прям

Элдор Суванов, почитайте статью. Там рассмотрены основные варианты структур, реализующих решение вашей проблемы, соответствуюзих им классов и соответствующих атрибутам этих классов запросов sql.
spoilerВы должны были сначала поискать, потом сюда писать вопрос. Публиковать вопросы, ответ на которые легко найти поиском в интернете, запрещено (п.2.2 Регламента). Я, к примеру, нашел за 5 секунд по запросу «sql цепочка родителей»

• ORM Hibernate, это библиотека для java. Конечно же я сюда пришел с последнюю очередь, в рунете не нашел оптимального решения.
• Элдор Суванов, да при чем тут ORM? Вы статью прочтите. ORM там существенной роли не играет - основная суть как раз между строк про ORM. Разобраны основные способы хранения деревьев в БД, ваш способ, как я понял, там первый. И разобраны запросы SQL для основных действий с этой структурой - получения предков, потомков, изменения и удаления. То, что оно привязано к конкретной ORM, вообще дело десятое.

  Впрочем, вам тут уже накидали более-менее готовых образцов…