CONCAT все родители категории

Question

Пример базы данных (категории в модели вложенного набора):

CREATE TABLE `category` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `lft` int(11) NOT NULL,
    `rgt` int(11) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

INSERT INTO category(name, lft, rgt) values("Primary", 0, 1000);
INSERT INTO category(name, lft, rgt) values("Secondary", 1, 500);
INSERT INTO category(name, lft, rgt) values("Tertiary", 2, 20);
INSERT INTO category(name, lft, rgt) values("Tertiary2", 21, 30);

Я хочу получить всех родителей нижних дочерних категорий (вероятно, concat?) Следующим образом:

Primary > Secondary > Tertiary
Primary > Secondary > Tertiary2

Помимо того, что я играю с BFTWEEN LFT AND RGT и CONCAT, я довольно застрял и мог бы использовать некоторые советы и информацию для получения требуемых результатов. Спасибо!

SQL Fiddle

Answer 1

Как насчет следующего запроса?

SELECT 
  GROUP_CONCAT(parent.name SEPARATOR '/') as parent_name
FROM category AS node,
        category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.lft;

Вернет запрошенный вывод:

Primary
Primary > Secondary
Primary > Secondary > Tertiary
Primary > Secondary > Tertiary2

Тестируемый @ http://sqlfiddle.com/

Если вам нужны только листовые узлы

Если я правильно прочитал исходный вопрос, ваш ожидаемый результат должен быть:

Primary > Secondary > Tertiary
Primary > Secondary > Tertiary2

Этого также можно легко добиться, слегка изменив приведенный выше запрос на следующее:

SELECT 
  GROUP_CONCAT(parent.name SEPARATOR '/') as parent_name
FROM category AS node,
        category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.lft, node.rgt
HAVING node.lft = (node.rgt - 1)
ORDER BY node.lft;

Однако это предполагает, что все ваши листовые узлы (как и должны) имеют rgt = lft + 1!!

Тест @ http://sqlfiddle.com/

Answer 2

Прочитайте эту прекрасную статью Майка Хиллиера " Управление наследственными данными ".

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

• Структура таблицы
• Как сделать запрос по-разному
  • Поиск узлов, путей и результатов полного дерева
• Обработка вставки новых данных
• Обработка удаления данных

Наконец, я понимаю, что эта статья прямо не отвечает вашему требованию генерировать подобные результаты непосредственно в SQL.

Primary > Secondary > Tertiary
Primary > Secondary > Tertiary2

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

Обычно это делается на уровне приложения, так как обычно эти языки гораздо больше подходят для него (и когда вы можете запрашивать данные, вы можете получить значения глубины, которые делают этот цикл довольно простым).