Searching a nested set

Question

I've got a MySQL table that acts like a nested set in order to contain a hierarchy of categories. The table schema looks like:

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(200) NOT NULL,
  `parent_id` int(11) default NULL,
  `lft` int(11) default NULL,
  `rgt` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index_categories_on_parent_id_and_name` (`parent_id`,`name`)
)

lft and rgt define the left and right boundaries of a node (the way a nested set works is that each node's id falls within its parent's boundaries), and parent_id specifies the parent node. The unique index allows there to be multiple categories with the same name, as long as they don't have the same parent.

I am trying to figure out a proper way to find a specific node in the set, based on hierarchy. For instance, if I look for foo/bar/baz, I want to retrieve the node named baz, whose parent is named bar, who's parent is named foo. Obviously, I can't just search by name, because there could be multiple categories with the same name.

The way I can think of doing this is to find the topmost category, then find each subsequent category with the given name whose parent id is that of the previously found category, but this doesn't seem very efficient to me. Is there a better way to search a nested set?

Answer 1

i've seen this before in a php project that was handed to me, and ugh, it's just bad.. if you can, break it into at least 2 tables; have at least 1 for categories and 1 for items, so you can join.. either way you're going to need to do multiple queries i'm afraid

Answer 2

TopVar = 'foo'
MidVar = 'bar'
BotVar = 'baz'

SELECT D0.*
FROM categories D0, categories D1, categories D2
WHERE D0.name = :BotVar
  AND D0.lft > D1.lft
  AND D0.rgt < D1.rgt
  AND D1.name = :MidVar
  AND D1.lft > D2.lft
  AND D1.rgt < D2.rgt
  AND D2.name = :TopVar;

-Al.