I have a query which calls a 'pages' table to get a pages data, then additional information page information of its parent is called. The problem is that it only gets the parent of the queried page, however if the parent has a parent I would like to get this as well, in a continuing fashion until there is no parent.
Here is my current query:
SELECT pages.*, CONCAT_WS("/", parent.page_url, pages.page_url) as abs_url FROM pages LEFT JOIN pages AS parent on pages.page_parent_id = parent.page_id WHERE pages.page_id = 1
What is the best and most optimised method to get this result? Is there a modification that can be made to the query to do the job or should I update all relevant URLs through the database base every time a URL modification is made?
Any feedback is greatly appreciated.
