MySql sql recursive loop

Question

I am building a navigation menu from a list of pages. The table is like this:

Table name: pages

id | type | parent | name
-------------------------------
1,    1,     null,   root1
2,    1,     null,   root2
3,    2,     2,      home
4,    2,     3,      child
5,    2,     4,      sub_child
6,    3,     5,      sub_sub_child


type:
1 = root page / site
2 = page
3 = ...

My problem is that from any page, I have to find the root page. I have a column parent that refers to the parent page, except for the root pages.

I can have multiple root pages in the table, but each page has only one parent.

Could somebody help me to write a recursive query ?

I'm trying to use this query, but it doesn't work:

with recursive pages (id, parent) as 
(
    select pages.id, 
    pages.parent, 
    from pages
    where pages.id = 4

union all
    select pages.id, 
    pages.parent,
    from pages
    inner join pages p on p.id = pages.parent
)
select id
from pages;

Thanks

MySQL's is very limited. They don't have support for recursive queries (or any other modern SQL feature that is). If you need more advanced functionality you might consider upgrading to PostgreSQL — user330315
– user330315, Commented Jun 20, 2012 at 12:33

LiuYan 刘研 · Accepted Answer · 2012-06-20 13:25:33Z

2

My farovite trick to handle tree structured data in database is add a column FullID to table to avoid complex (parhaps recursive) SQLs/Stored Procedures.

FullID     id  parent   name
-----------------------------
1          1   null     root1
2          2   null     root2
2.3        3   2        home
2.3.4      4   3        child
2.3.4.5    5   4        sub_child
2.3.4.5.6  6   5        sub_sub_child

So, to find the root page id, just extract the first part of FullID via SQL or your application language.

If using SQL, you can use the following SQL to get the root id.

-- MySQL dialect
select substring_index(FullID,'.',1) as RootID from table;

-- SQL Server dialect
select case charindex('.', FullID) when 0 then FullID else substring(FullID, 1, charindex('.', FullID)-1) end as RootID from table

To delete a node and it's children

DELETE table WHERE id=<CURRENT_NODE_ID> OR FullID LIKE '<CURREN_NODE_FULLID>.%'

To move a node and it's children

-- change the parent of current node:
UPDATE table
SET parent=<NEW_PARENT_ID>
WHERE id=<CURRENT_NODE_ID>

-- update it's FullID and all children's FullID:
UPDATE table
SET FullID=REPLACE(FullID,<CURRENT_NODE_PARENT_FULLID>, <NEW_PARENT_FULLID>)
WHERE (id=<CURRENT_NODE_ID> OR FullID LIKE '<CURRENT_NODE_FULLID>.%')

Note

This trick is only applied on limited tree level cases, or the FullID can't hold long content if tree level is too deep.

edited Jun 20, 2012 at 13:25

answered Jun 20, 2012 at 12:12

LiuYan 刘研

1,6241 gold badge16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

blaazzze Over a year ago

another trick, but sound great. It will be more easy than the easy solution "using php". There is one thing that I don't like... When I move a page, I will have to change all children and subchildren. And at this time, I will have to use a recursive query. So we return to begin of the problem. Thank you anyway ;-)

LiuYan 刘研 Over a year ago

To change the parent of a node, you can also use two simple SQLs, 1. change the parent of current node: UPDATE table SET parent=<NEW_PARENT_ID> WHERE id=<CURRENT_NODE_ID>. 2. update it's FullID and all children's FullID:

UPDATE table SET FullID=REPLACE(FullID,<CURRENT_NODE_PARENT_FULLID>, <NEW_PARENT_FULLID>) WHERE (FullID LIKE '<CURRENT_NODE_FULLID>.%' OR id=<CURRENT_NODE_ID>)

blaazzze Over a year ago

Yes, thanks you. I didn't know the replace function. With that, it will be really easy. Thanks You !

geekchic Over a year ago

I'd argue that the pattern I've described is slightly more scalable.

LiuYan 刘研 Over a year ago

@geekchic, i agree with you, but there's some problems here: (1) the id is auto-increment, but the nested set model use order of visiting to numbering, so if a node with highest id is the root node, you can't use id as order of visiting. (2) if using order of visiting to numbering, then it will introduce renumbering issue if tree data is modified (node added/deleted/moved), renumbering is expensive as wikipedia page said. (3) as @michael mentioned, he has more than 1 roots

|

geekchic · Accepted Answer · 2012-06-20 12:50:13Z

As other posters have commented, there doesn't seem to be support for this in MySQL. You could restructure your table using the nested set model to get rid of the need for hierarchical queries.

Example

Instead of the parent column, you have leftid, rightid and is_root.

id | type | leftid | rightid | is_root |  name
------------------------------------------------
1,    1,     1,      2         1          root1
2,    1,     3,      12        1          root2
3,    2,     4,      11        0          home
4,    2,     5,      10        0          child
5,    2,     6,      9         0          sub_child
6,    3,     7,      8         0          sub_sub_child

Then to find the parents of any given record, you just find the records with leftid less than and rightid greater than that record. Use the is_root column to get the ultimate root record.

npe · Accepted Answer · 2012-06-20 11:53:47Z

0

The WITH RECURSIVE clause that you are using is applicable for PostgreSQL databases, not for MySQL. Oracle has it's CONNECT BY ... START WITH ... and it seems, that recursive queries are done differently in every database.

MySQL however, has no support for recursive / hierarchical queries. You need to procedurally loop through rows to find parents up to the root.

See how hierarchical queries (like Oracle's CONNECT BY) can be emulated in MySQL.

answered Jun 20, 2012 at 11:53

npe

15.8k1 gold badge59 silver badges59 bronze badges

4 Comments

blaazzze Over a year ago

thanks for your answer, that explain why it doesn't work. And thank you for the link, but I have no idea what I am reading...

npe Over a year ago

Well, you can do it from Java/PHP/Whatever language you're using, by selecting parent id and applying it as id in the next it loop iteration until you get parent id = null, meaning you got an id pointning at the root element. Quite inefficient, but working. Or, try to understand what you're reading ;-)

blaazzze Over a year ago

Yes, I can use php, it will be easy, but I try to to a beautiful code. So i try to use START WITH. I don't use often SQL, so it's a little hard to understand what it said, and because english is not my natural language. But I try ;-)

user330315 Over a year ago

WITH recursive is ANSI SQL and is not only supported by PostgreSQL but also by Oracle 11.2 (in addition to connect by), Firebird, DB2, SQL Server, Sybase and Teradata.

blaazzze · Accepted Answer · 2012-06-20 13:09:18Z

0

I think I will add some modification to my database. After talking, I can remove the column type and add some table to class the different pages. So the structure is changed. I think I will use a php loop. It's not beautiful, but it work and it's no too slow... there will never be 200 children one under other... But I keep in mind the START WITH.

Thank you guys

answered Jun 20, 2012 at 13:09

blaazzze

1323 silver badges14 bronze badges

Collectives™ on Stack Overflow

MySql sql recursive loop

4 Answers 4

8 Comments

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related