php - MySQL嵌套设置层次结构漂亮的url菜单数组

Question

我想从我的嵌套类别列表中创建一个漂亮的 url 菜单数组。

表格:

• categories (lft, rgt, id)
• categories_description (cat_id, lang_id, name)
• seo_url (cat_id, prod_id, man_id, url)

表categories包含所有类别，类别名称来自表categories_description，漂亮的网址来自表seo_url。

有没有办法组合所有三个表并在 1 个查询中获取整个菜单数组？

例如:

• parent (with name of parent)
• parent/subcat (with name of subcat)
• parent/subcat2 (with name of subcat2)
• parent2 (with name of parent2)
• parent2/subcat32 (with name of subcat32)
• parent2/subcat42/subcat23 (with name of subcat23)
• parent3 (with name of parent3)
• parent4 (with name of parent4)
• parent4/subcat4 (with name of subcat4)

Answer 1

如果它们仅嵌套到设定的深度(例如，根据您的示例，最多 3 层)，那么您应该可以。你最终会得到一些列为空的结果，你必须在代码中而不是 sql 查询中满足这一点。

很难用您提供的数据给您一个具体的例子但它会是这样的(注意这是未经测试的)

select parent_description.name as parent_name,
parent_url.url as parent_url,
child_description.name as child_name,
child_seo_url.url as child_url,
grandchild_description.name as grandchild_name,
grandchild_seo_url.url as grandchild_url,
from categories as parent
join category_description as parent_description on parent.id=parent_description.cat_id
join seo_url as parent_seo_url on parent.id=parent_seo_url.cat_id
left outer join categories as child on parent.id=child.parent_id
left outer join category_description as child_description on child.id=child_description.cat_id
left outer join seo_url as child_seo_url on child.id=child_seo_url.cat_id
left outer join categories as grandchild on grandchild.id=child.parent_id
left outer join category_description as grandchild_description on grandchild.id=grandchild_description.cat_id
join seo_url as grandchild_seo_url on grandchild.id=grandchild_seo_url.cat_id

应该给出类似的输出

parent_name | parent_url | child_name | child_url | grandchild_name | grandchild_url
parent      | url        | NULL       | NULL      | NULL            | NULL
parent      | url        | child      | url       | NULL            | NULL
parent      | url        | child      | url       | grandchild      | url

你应该能够从中渲染 html