Categories and Subcategories

Databases
1

Hello,

I found this tutorial http://sqllessons.com/categories.html and I want to use the described method as it’s better than a recursive call but I have two problems:

  1. How will I know the deepest level? In some page I may have 2 and in another I may have 20. Am I missing something or this method can be used only if you know the deepest level prior runtime!?

  2. I have a multilingual database design, how can I change the queries to work with my table design below:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL,
  `position` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id_fk` (`parent_id`),
  CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `categories_locale` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(100) NOT NULL DEFAULT '',
  `name` varchar(40) NOT NULL DEFAULT '',
  `category_id` int(11) NOT NULL,
  `locale_id` smallint(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `locales` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `code` char(2) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Your help would be greatly appreciated.

2

one way to know the deepest level is to inspect your data periodically

alternatively, you can put in some code to check upon insertion of a new sublevel, make sure it isn’t too deep, and give an error message if it is

my preferred methoid is as follows: for retrieval, write the query with one more left outer join than you have levels, to the best of your knowledge, but if the lowest level of the query returns something, how do you know there aren’t more levels below that? you don’t, so the code should handle this gracefully, like perhaps with a “more…” type link which then allows the user to click through and drill down some more levels, so the query wouldn’t start out just looking to expand root nodes, it would expand any node including one partway down the levels

that isn’t exactly recursion but it does require the user to initiate a drill down

i hope none of the terminology i’ve used here or in that article (which i wrote) confuses you, please holler if it did

3

Hello,

Thanks for your reply. I’m actually struggling. Here are my tables:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `position` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id_fk` (`parent_id`),
  CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `categories` (`id`, `position`, `parent_id`)
VALUES
	(1,1,NULL),
	(2,2,NULL),
	(3,1,1),
	(4,2,1),
	(5,1,4),
	(6,2,4),
	(7,3,1),
	(8,1,2),
	(9,1,8),
	(10,2,8),
	(11,2,2);

CREATE TABLE `categories_locale` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(100) NOT NULL DEFAULT '',
  `name` varchar(40) NOT NULL DEFAULT '',
  `path_cache` text,
  `category_id` int(11) NOT NULL,
  `locale_id` smallint(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `categories_locale` (`id`, `slug`, `name`, `path_cache`, `category_id`, `locale_id`)
VALUES
	(1,'animals','Animals',NULL,1,1),
	(2,'plants','Plants',NULL,2,1),
	(3,'birds','Birds',NULL,3,1),
	(4,'mammals','Mammals',NULL,4,1),
	(5,'elephant','Elephant',NULL,5,1),
	(6,'mouse','Mouse',NULL,6,1),
	(7,'reptiles','Reptiles',NULL,7,1),
	(8,'flowers','Flowers',NULL,8,1),
	(9,'rose','Rose',NULL,9,1),
	(10,'tulip','Tulip',NULL,10,1),
	(11,'trees','Trees',NULL,11,1);

What I need is a result set allowing me to build the tree in the following manner:

<ul class="tree">
	<li>Animals
		<ul>
			<li>Birds</li>
			<li>Mammals
				<ul>
					<li>Elephant</li>
					<li>Mouse</li>
				</ul>
	 		</li>
			<li>Reptiles</li>
		</ul>
	</li>
	<li>Plants
		<ul>
			<li>Flowers
				<ul>
					<li>Rose</li>
					<li>Tulip</li>
				</ul>
  			</li>
			<li>Trees</li>
 		</ul>
	</li>
</ul>

Your help would be greatly appreciated.

4

here’s the query –

SELECT cloc.name 
     , cloc1.name AS name1
     , cloc2.name AS name2
  FROM categories AS c
INNER
  JOIN categories_locale AS cloc
    ON cloc.category_id = c.id
LEFT OUTER
  JOIN categories AS c1
    ON c1.parent_id = c.id
LEFT OUTER
  JOIN categories_locale AS cloc1
    ON cloc1.category_id = c1.id      
LEFT OUTER
  JOIN categories AS c2
    ON c2.parent_id = c1.id
LEFT OUTER
  JOIN categories_locale AS cloc2
    ON cloc2.category_id = c2.id      
 WHERE c.parent_id IS NULL
ORDER
    BY c.position
     , c1.position  
     , c2.position

here are the results –

name     name1     name2
Animals  Birds     NULL
Animals  Mammals   Elephant
Animals  Mammals   Mouse
Animals  Reptiles  NULL
Plants   Flowers   Rose
Plants   Flowers   Tulip
Plants   Trees     NULL

processing these results into the nested ULs is then the job of your application language (php or whatever)

5

Thanks a lot. I’ll try to write the code.