CONCAT all parents of category

Question

Sample database (Categories in a Nested Set Model):

CREATE TABLE `category` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `lft` int(11) NOT NULL,
    `rgt` int(11) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

INSERT INTO category(name, lft, rgt) values("Primary", 0, 1000);
INSERT INTO category(name, lft, rgt) values("Secondary", 1, 500);
INSERT INTO category(name, lft, rgt) values("Tertiary", 2, 20);
INSERT INTO category(name, lft, rgt) values("Tertiary2", 21, 30);

I want to obtain all the parents of the bottom child categories (probably concat?) like this:

Primary > Secondary > Tertiary
Primary > Secondary > Tertiary2

Besides playing with BETWEEN lft AND rgt and CONCAT, i'm pretty stuck and could use some tips and information about getting the required results. Thanks!

SQL Fiddle

Answer 1

How about following query?

SELECT 
  GROUP_CONCAT(parent.name SEPARATOR '/') as parent_name
FROM category AS node,
        category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.lft;

Will return the requested output:

Primary
Primary > Secondary
Primary > Secondary > Tertiary
Primary > Secondary > Tertiary2

Testable @ http://sqlfiddle.com/#!9/4f4e97/2

In case you only want the leaf nodes

If I read correctly the original question your expected output should be:

Primary > Secondary > Tertiary
Primary > Secondary > Tertiary2

This is possible to achieve easily too by modifying the above query slightly to following:

SELECT 
  GROUP_CONCAT(parent.name SEPARATOR '/') as parent_name
FROM category AS node,
        category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.lft, node.rgt
HAVING node.lft = (node.rgt - 1)
ORDER BY node.lft;

This however assumes that all your leaf nodes (as they should) have rgt = lft + 1 !!

Test @ http://sqlfiddle.com/#!9/57acd4/1

Answer 2

Check out this excellent article on Managing Heirarchial Data by Mike Hillyer.

It explains quite a good method of not only structuring you data but also how to query and manipulate it including some differences between Adjacency List model and Nested Set model.

• Table structure
• How to query it in various ways
  • Finding Nodes, Paths and Full Tree results
• Handling insertion of new data
• Handling deletion of data

Finally, I realise this article doesn't expressly answer your requirement of generating results like these directly in SQL.

Primary > Secondary > Tertiary
Primary > Secondary > Tertiary2

You probably won't find much good reading on any decent ways of doing this without expensive loops or terribly difficult to maintain queries.

Typically this is done on an application level as generally those languages are much more suited for it (and when you can query your data you can produce depth values which make this looping quite easy to do).