MySQL query for page ancestor

Question

I have a MySQL table of pages with these fields (id, parentID, name, content).

The page hierarchy is:

 - About Us 
   - History 
     - 2000
     - 2001

If I'm on page "2001" how can I create a query to find that this page's top-level ancestor is "About Us"?

Take a look at [this link](http://dev.mysql.com/tech-resources/articles/hierarchical-data.html)... What you have described is essentially an adjacency list approach. [This question/answers](http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database) may also be of use to you. They list some of the numerous methods and schemas used to accomplish storing and querying hierarchical data. — prodigitalson, Jun 21 '11 at 01:27
thanks for the quick comment. Which query gets me the parent knowing the id of the page im on? — user520300, Jun 21 '11 at 01:30
While i realize you are looking for a specific answer - do not cherry pick the article, read the whole thing. After reading you can post a new question or update this one with specifics if you dont understand. But there are alot of things that are mentioned that important and more than that help you understand why it works. I didnt post this as answer nor did i point to specifics for this reason :-) — prodigitalson, Jun 21 '11 at 01:34
you may go with nested set model if you want your selection query to work faster. — Shuhel Ahmed, Jun 21 '11 at 01:40

Brad Mace · Answer 1 · 2011-06-21T01:39:36.660

Once you redesign your table to follow the Nested Sets model, the query would be

SELECT parent.name
FROM pages AS node,
pages  AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = '2001'
ORDER BY parent.lft
LIMIT 1

which will give you "About Us"

If you are only allowing a specific level of nesting, you can do a bunch of left joins like the example shown on that page:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t4.name = 'FLASH';

but if you want to support nesting to an arbitrary depth, nested sets are about the only game in town. Take the time to read through that whole page. It's probably more involved than you were hoping, but it's very powerful once you see how it works.

score 0 · Answer 2 · answered Jun 21 '11 at 01:33

0

SELECT c.id, c.name, c.content, (SELECT p.name FROM table2 as p WHERE p.id = c.parentID) AS parent FROM table1 as c;

Or use a join on parentID.

answered Jun 21 '11 at 01:33

Tieson T.

20,774
6
77
92

If you go more than one layer deep this will fail. Best to do this programatically. – Suroot Jun 21 '11 at 01:35

score 0 · Accepted Answer · answered Jun 21 '11 at 01:34

0

You can use something like the following pseudocode to do this in a recursive manner.

GetParent(current_id)
  get tuple from mysql with id = current_id
  If parentId = 0
    Return tuple (we are at top)
  Else 
    Return GetParent(parentId)

answered Jun 21 '11 at 01:34

Suroot

4,315
1
22
28

score 0 · Answer 4 · answered Jun 21 '11 at 01:37

I'll assume that the ID of the parent of the '2001' page is 5.

SELECT name FROM page_table WHERE id=(SELECT parentID FROM page_table WHERE id=5)

As long as you know your depth level within the hierarchy, you can construct subqueries to find your way back to the top.

MySQL query for page ancestor

4 Answers4