2

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;
Community
  • 1
  • 1
Mahavir Munot
  • 1,464
  • 2
  • 22
  • 47
  • Well, you're using an aggregate function with no GROUP BY clause. Is that based on an example from within the article? – Strawberry Dec 06 '13 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 Dec 06 '13 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 Dec 06 '13 at 13:11
  • Order by directory http://sqlfiddle.com/#!2/1a0f9/7 – Mahavir Munot Dec 19 '13 at 12:35

1 Answers1

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;
Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • awesome!!! it works like charm. Could you suggest me which suggest me which method should I use to handle large number of rows (million)? http://stackoverflow.com/questions/20422497/mysql-modified-preorder-tree-to-create-url/20425158?noredirect=1#20425158 or http://stackoverflow.com/questions/20280104/mysql-query-to-create-seo-friendly-url-from-given-table-structure – Mahavir Munot Dec 06 '13 at 15:09
  • 1
    here is the output of you query http://sqlfiddle.com/#!2/e91e14/3 This may help others as well – Mahavir Munot Dec 06 '13 at 15:14
  • Optimization's not really my thing – Strawberry Dec 06 '13 at 15:16