MySql Modified preorder tree to create url

Question

This question is based on my previous question on stackoverflow. This time I am trying to generate urls for all the rows in pages table using the Modified Preorder Tree method.

Here are the MySql Tables: [Note: I have added the 'parent' column just for the viewers to understand the parent-child relationship in the category table. The actual Modified Preorder Tree method does not use parent column (parent-child relationship)]

Category table:

Modified Preorder Traversal Tree

Pages table

pages table

I am trying to write a mysql query that will generate URLs for all the rows in the pages table using the category table producing the below output.

Expected rows:

/content/article/array/oops/classes
/content/article/array/oops/objects
/content/article/php/index
/content/article/php/quiz
/content/article/php/strings/strstr
/content/article/php/strings/str_substr
/content/blog/something1
/content/blog/something2
/content/blog/java/test-java
/content/blog/java/final-method
/content/about-us
/content/contact-us

Here is what I tried on category table referring to Managing Hierarchical Data in MySQL. The below SQL query retrieve single path for title 'oops' in category table.

SELECT concat('/',group_concat(parent.title separator '/')) as url
FROM category AS node, category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.title = 'oops'
ORDER BY parent.lft;

Well, you're using an aggregate function with no GROUP BY clause. Is that based on an example from within the article? — Strawberry
– Strawberry, Commented Dec 6, 2013 at 12:49
@Strawberry Yes correct, I am referring the section that shows how to retrieve single path. Is there any other way to write query to generate urls for all the rows in the pages table without using the aggregate function? — Mahavir Munot
– Mahavir Munot, Commented Dec 6, 2013 at 12:51
@Strawberry it is just added for the viewers to understand, I have added a note for the viewers in the above question now. — Mahavir Munot
– Mahavir Munot, Commented Dec 6, 2013 at 13:11

Strawberry · Accepted Answer · 2013-12-06 13:29:59Z

1

Something like this should suffice...

SELECT CONCAT_WS('/',x.path,y.page_name) full_path
  FROM
     ( SELECT node.id, GROUP_CONCAT(parent.title ORDER BY parent.lft SEPARATOR '/') path
         FROM category node
         JOIN category parent
           ON node.lft BETWEEN parent.lft AND parent.rgt
        GROUP 
           BY node.title
        ORDER 
           BY node.lft
     ) x
  JOIN pages y
    ON y.category_id = x.id;

answered Dec 6, 2013 at 13:29

Strawberry

34k14 gold badges43 silver badges57 bronze badges

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

3 Comments

Mahavir Munot Over a year ago

awesome!!! it works like charm. Could you suggest me which suggest me which method should I use to handle large number of rows (million)? stackoverflow.com/questions/20422497/… or stackoverflow.com/questions/20280104/…

Mahavir Munot Over a year ago

here is the output of you query sqlfiddle.com/#!2/e91e14/3 This may help others as well

Strawberry Over a year ago

Optimization's not really my thing

Collectives™ on Stack Overflow

MySql Modified preorder tree to create url

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related