MySQL nested set hierarchy pretty url menu array

Question

I would like to create a pretty url menu array from my nested set list of categories.

The tables:

categories (lft, rgt, id)

categories_description (cat_id, lang_id, name)

seo_url (cat_id, prod_id, man_id, url)

Table categories holds all categories, category names come from table categories_description and pretty urls come from table seo_url.

Is there a way to combine all three tables and fetch the whole menu array in 1 query?

For example:

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)

You should only use a table like this: id idParent order name link The categories will have an empty link. After that, based on the parent's id you can create a tree like this: stackoverflow.com/questions/4284616/php-array-tree-sorting — Catalin
– Catalin, Commented Oct 4, 2011 at 8:49

bumperbox · Accepted Answer · 2011-10-04 08:58:20Z

if they are only nested to a set depth (eg 3 levels max as per your example), then you should be able to. you would end up with some columns being null, which you would have to cater for in code, rather then the sql query.

it is hard to give you a concrete example with the data you have provided but it wuld be something like this (note this is untested)

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

which should give an out put like

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

you should be able to render the html from that

sfussenegger · Accepted Answer · 2011-10-04 09:11:20Z

1

You could simply fetch all categories at once and build the hierarchy in code.

select * 
  from categories c 
  join categories_description d on d.cat_id = c.id 
  join seo_url u on d.cat_id = u.id;

I'm not very fluent in PHP anymore, but using a hash to lookup parent categories should work pretty well. Finally, you'll have to sort the whole list according to your needs.

Performance wise, I wouldn't worry too much. Building and sorting the menu should still be faster than the data fetching itself (given that the code is well written) as we're talking about a few ms here. The whole structure would certainly be a good candidate for caching though - but don't do it before you have to.

answered Oct 4, 2011 at 9:11

sfussenegger

36.2k17 gold badges101 silver badges120 bronze badges

Comments

score 0 · Accepted Answer · 2011-10-06 08:49:17Z

0

Thanks for the solutions. I've decided to use this query because it exports the menu array in the same way as I wrote in my question.

SELECT node.cat_id, node.lft, node.rgt, GROUP_CONCAT(su.url ORDER BY parent.lft  SEPARATOR "/" ) AS path, (COUNT(parent.lft) - 1) AS depth, cd.name AS name
FROM categories AS node
JOIN categories AS parent ON node.lft BETWEEN parent.lft AND parent.rgt
JOIN seo_url AS su ON parent.cat_id = su.cat_id
JOIN categories_description AS cd ON node.cat_id = cd.cat_id
WHERE parent.lft >= 1
GROUP BY node.cat_id
ORDER BY node.lft

edited Oct 6, 2011 at 8:49

answered Oct 6, 2011 at 7:43

user554992

Collectives™ on Stack Overflow

MySQL nested set hierarchy pretty url menu array

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related