Древовидная структура SQL родительский дочерний
У меня есть следующие данные:
id parent_id
------------------ ------------------
Editor null
Printer Editor
TextWritingProggie Printer
LaTeX TextWritingProggie
OOfficeWriter TextWritingProggie
PhoneBook TextWritingProggie
Я запрашиваю идентификатор телефонной книги и всех ее родителей. Это должна быть иерархия, которая ведет к редактору, поскольку его родительский элемент имеет нулевое значение, и он подключается через принтер, который подключается через TextWritingProggie, который затем подключается к телефонной книге.
Я полагаю, что использование SQL является более предпочтительным, однако может потребоваться обработка через PHP.
Вот что я получил до сих пор, но он останавливается после записей, которые не содержат TextWritingProggie
SELECT * FROM acl_resources
WHERE id = 'TextWritingProggie' OR parent_id = 'TextWritingProggie'
ORDER BY COALESCE(parent_id, id), parent_id IS NOT NULL, id
Любая помощь будет отличной
Решение
Некоторые продукты СУБД предлагают встроенные иерархические запросы. Например, Oracle SQL имеет START WITH ... CONNECT BY синтаксис, в котором вы можете использовать этот вид запроса, чтобы получить свой результат.
SELECT id, SYS_CONNECT_BY_PATH(parent_id, '/'), LEVEL -- Oracle
FROM res
WHERE id='PhoneBook'
CONNECT BY prior id=parent_id
ORDER BY LEVEL DESC
Но не MySQL. Вы должны подделать это как-то. Одним из способов является получение данных и построение иерархии в памяти вашего приложения.
Другой способ с последовательностью LEFT JOIN операции, которые длиннее, чем ожидаемая глубина иерархии. Этот запрос немного уродлив, но работает. Каждая строка такого запроса содержит «генеалогию» строки. Вот пример для ваших данных (http://sqlfiddle.com/#!2/bab1d/4/0):
select a.id a, b.id b, c.id c, d.id d, e.id e, f.id f, g.id g /*MySQL*/
from res a
left join res b on a.parent_id = b.id
left join res c on b.parent_id = c.id
left join res d on c.parent_id = d.id
left join res e on d.parent_id = e.id
left join res f on e.parent_id = f.id
left join res g on f.parent_id = g.id
where a.id = 'PhoneBook'
Результатом этого конкретного запроса является
| A | B | C | D | E | F | G |
|-----------|--------------------|---------|--------|--------|--------|--------|
| PhoneBook | TextWritingProggie | Printer | Editor | (null) | (null) | (null) |
Логика, которая закодирована в CONNECT BY PRIOR id = parent_id в Oracle иерархический запрос отображается здесь как повторный ON пункты для повторного LEFT JOIN операции.
Другие решения
Других решений пока нет …