Как выбрать все родительские категории в одну колонку?

Question

[Aljo]

Здравствуйте!
Есть интернет-магазин с 6к товарами.
Есть таблица site_resources в которой содержаться записи по каждой странице сайта. Уникальный идентификатор - id, поле названия страницы - title, поле родительского ресурса - parent, если parent = 0, значит это корневая страница.
Я хочу сделать выгрузку товаров (название, иерархия, title, description) и в столбце Иерархия вывести все родительские категории в иерархическом порядке, разделенные запятой.
Никак не пойму можно ли так сделать, если да, то как.
Какой-то рекурсивный self-join пока parent != 0
Сейчас запрос выглядит так, т.е. просто выбирается название родителя первого уровня.

SELECT sr.title 'Название', sr.url 'Урл', sr2.title 'Иерархия', CASE WHEN meta1.value IS NULL THEN sr.title ELSE meta1.value END 'Title', CASE WHEN meta2.value IS NULL THEN sr.description ELSE meta2.value END 'Description'
FROM `site_resources` sr
LEFT JOIN `site_resources` sr2 ON sr2.id = sr.parent
LEFT JOIN `site_extrafields` meta1 ON meta1.resid = sr.id AND meta1.fieldid = 4
LEFT JOIN `site_extrafields` meta2 ON meta2.resid = sr.id AND meta2.fieldid = 5
WHERE sr.published = 1 AND sr.template = 10

Подскажите пожалуйста в какую сторону копать, что почитать?

Answer 1

[Deltas]

Набросал некоторые примеры, приближенные к описанной задаче.
Постановка задачи не полная (модель, форматы данных, сортировка, критерии отбора, какая СУБД - ничего не описано), но суть думаю реализовал. + обратите внимание на ответ Руслан ., там информации уже достаточно для реализации. + Полезно прочитать как "Вычисление рекурсивного запроса" например в документации Postgre

Демонстрационный "расширенный" пример см. по ссылке + укороченный и чуть скорректированный под ваш пример вариант кода на Postgre ниже

with recursive cte(id, title, url, hierarchy) as (

	SELECT 
		id,
		title,
		url,
		null::text as hierarchy
	from 
		site_resources
	where 
		parent = 0
		--and published = 1 AND template = 10 --В условиях задачи не сказано про эти критерии, но в примере они есть. Раскомментить, если нужно
	
	union all
	
	SELECT 
		sr.id,
		sr.title,
		sr.url,
		coalesce(cte.hierarchy || ', ', '') || sr.title
	from 
		cte
	inner join
		site_resources as sr on
			cte.id = sr.parent
			--and sr.published = 1 AND sr.template = 10 --Аналогично комменту выше + если надо так отфильтровать только корневые элементы, то вся эта строка тут не нужна

)
SELECT 
  sr.title                        		as 'Название',
  sr.url 				        as 'Урл',
  sr.hierarchy                    		as 'Иерархия',
  coalesce(meta1.value, sr.title) 		as 'Title',
  coalesce(meta2.value, sr.description) as 'Description'
FROM 
  cte as sr
LEFT JOIN `site_extrafields` meta1 ON meta1.resid = sr.id AND meta1.fieldid = 4
LEFT JOIN `site_extrafields` meta2 ON meta2.resid = sr.id AND meta2.fieldid = 5;

Answer 2

[Руслан .]

Можно попробовать рекурсивный CTE, только нужно убедиться, что глубина рекурсии не очень большая, не больше 50 например.
Вот тут есть пример.
https://learnsql.com/blog/recursive-cte-sql-server/
Ну и реализация будет зависеть о вашей платформы, т.к. есть небольшие различия в синтаксисе.

Comments

[Aljo]

Спасибо, уже ранее находил статьи и примеры на SO. Но вот никак не могу догнать, особенно почему практически во всех примерах еще присутствует UNION. Мне пока банально непонятно как это работает...

[Руслан .]

Aljo, первая часть запроса - первый шаг рекурсии. А вторая, то что выпоняется на последующих шагах.
Обратите внимание на поле level.
В первом запросе оно 1 AS level, а в остальных level +1. На наждом шаге рекурсии поле level увеличивается.

[Aljo]

Руслан ., да, спасибо, это я понял, за 5.5 часов ничего не получилось все равно...