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

Question

[Элдор Суванов]

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

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

Comments

[Алан Гибизов]

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

[Модератор]

Фрагменты кода надо размещать в виде текста и оборачивать тэгом code для корректного отображения. Удобно делать кнопкой </>
Это обязательно, см.п.3.8 Регламента.
Сюда же относится traceback, ввод и вывод в консоли и другая структурированная текстовая инфа.

[Элдор Суванов]

Алан Гибизов, например

категория 1 / категория 5 / категория 7 / категория 20
категория 2 / категория 20 / категория 45
категория 46 / категория 70 / категория 80 / категория 99

Мы находится на странице (категория 80) нужно получить id (категория 46 / категория 70)
можно сделать sql запрос, чтобы получить нужные id, а не получать все категории, а потом вытаскивать из массива нужные id

[Михаил Ливач]

Дмитрий Котеров когда-то опубликовал библиотеку, которая генерировала SQL-запрос с джойнами . Ограничение было такое: надо было заранее знать верхний предел уровней вложенности. На выходе таблица со столбцами t1.id, t1.pid, t1.name, t2.id, t2.pid, t2.name ...
Жаль, форум "Лаборатории" давно не работает.

Answer 1

[Rsa97]

Не указано, что за сервер 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`

Comments

[Элдор Суванов]

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

[Rsa97]

Элдор Суванов, Несколько запросов будут гораздо затратнее.

[ThunderCat]

Элдор Суванов,

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

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

Answer 2

[Владислав Enokin]

На 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);

Comments

[Элдор Суванов]

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']])];

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

[Владислав Enokin]

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

Answer 3

[Алан Гибизов]

Элдор Суванов, почитайте статью. Там рассмотрены основные варианты структур, реализующих решение вашей проблемы, соответствуюзих им классов и соответствующих атрибутам этих классов запросов sql.

spoiler

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

Comments

[Элдор Суванов]

ORM Hibernate, это библиотека для java. Конечно же я сюда пришел с последнюю очередь, в рунете не нашел оптимального решения.

[Алан Гибизов]

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

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