CODEWITHSUNDEEP
mysqlsqlloopsrecursion
blaazzze
blaazzze

Reputation: 132

MySql sql recursive loop

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

Upvotes: 2

Views: 3851

Answers (4)

LiuYan 刘研
LiuYan 刘研

Reputation: 1624

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.

Upvotes: 2

blaazzze
blaazzze

Reputation: 132

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

Upvotes: 0

geekchic
geekchic

Reputation: 1566

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.

Upvotes: 1

npe
npe

Reputation: 15699

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.

Upvotes: 0

Related Questions