MySql修改preorder树来创建url [英] MySql Modified preorder tree to create url
问题描述
这个问题是基于我之前对
我试图编写一个mysql查询,将使用产生以下输出的类别表为页面表中的所有行生成URL。
预期行:
/ 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
我尝试了类别表,参考管理层次MySQL中的数据。以下SQL查询在类别表中检索标题oops的单个路径。
SELECT concat('/',group_concat(parent.title separator'/'))as url
FROM category AS节点,类别AS父
WHERE node.lft BETWEEN parent.lft和parent.rgt
AND node.title ='oops'
ORDER BY parent.lft;
这样的东西就够了...
SELECT CONCAT_WS('/',x.path,y.page_name)full_path
FROM
id,GROUP_CONCAT(parent.title ORDER BY parent.lft SEPARATOR'/')path
FROM类别节点
JOIN类别parent
ON node.lft BETWEEN parent.lft和parent.rgt
GROUP
BY node.title
ORDER
BY node.lft
)x
JOIN页面y
ON y.category_id = x.id;
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)]
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;
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;
这篇关于MySql修改preorder树来创建url的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!