MySQL Self-Join

У меня есть таблица (это не может быть изменено), как показано ниже: 

POST_ID | PARENT_ID | POST_NAME

1       | 0         | Services
4       | 1         | Development
5       | 4         | Magento
2       | 0         | Contact

Поле 'parent_id' ссылается на post_id, чтобы сформировать самореферентный внешний ключ. Можно ли написать один запрос для подключения записи и ее родителей на основе post_id?

Например, если бы у меня был post_id 5 (Magento), я мог бы написать запрос, чтобы получить следующие результаты: 

5 | Magento
4 | Development
1 | Services

Я знаю, что это довольно легко сделать с несколькими запросами, однако, хочу знать, возможно ли это с помощью одного запроса.

Спасибо :)

2 3
sql mysql database hierarchical-data

2 ответа:

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

Некоторые СУБД, такие как SQL Server 2005, Postgres 8.4 и Oracle 11g, поддерживают рекурсивные запросы, использующиеобщие табличные выражения с ключевым словом WITH (Также см. комментарий@Quassnoi ниже). Эта функция позволяет запросам, подобным этому, быть написанными с легкостью, но как @OMG Пони , упомянутый в комментарии выше, MySQL еще не поддерживает рекурсивные запросы.

Вы упомянули, что не можете внести какие-либо изменения в свою таблицу, но можете ли вы добавить дополнительную таблицу? Если да, то вам может быть интересно проверить следующую статью, в которой описывается Альтернативная модель (вложенная модель множества ), которая упрощает рекурсивные операции (возможно):

В кроме того, я также предлагаю проверить следующую презентацию @Bill Karwin , регулярного участника переполнения стека:

Модель таблицы замыканий, описанная в презентации, является очень хорошей альтернативой вложенному набору. Далее он описывает эту модель в своей книгеSQL Antipatterns (выдержка из главы на эту тему [PDF]).

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

2

Конечно, поскольку вы используете MySQL, я бы предложил изменить вашу модель данных на nested sets или materialized path.

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

2