Geek Answers Handbook

Get the number of items associated with a nested category in sql

I have two tables: categories and items. I saved the categories using a nested collection structure. Categories have elements. Elements can only be added to leaf nodes in the root category.

For example: Categories

Vehicles Bikes Bajaj Automobiles Art & Antiques Amateur Art

In this case, items can be added to the Bajaj category, cars and amateur art.

Let's say that in Bajaj there are 2 items, 5 items inside cars, 2 inside amateur art

For root level categories, I want to display the following:

 - Vehicles (7 items) - Art & Antiques (2 items)

How can i do this?

Here is a sql dump for working with some sample data

 -- -- Table structure for table `categories` -- CREATE TABLE IF NOT EXISTS `categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `title` varchar(64) COLLATE utf8_unicode_ci NOT NULL, `lft` int(11) NOT NULL, `lvl` int(11) NOT NULL, `rgt` int(11) NOT NULL, `root` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `IDX_3AF34668727ACA70` (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=28 ; -- -- Dumping data for table `categories` -- INSERT INTO `categories` (`id`, `parent_id`, `title`, `lft`, `lvl`, `rgt`, `root`) VALUES (22, NULL, 'Vehicles', 1, 0, 8, 22), (23, 22, 'Bikes', 2, 1, 5, 22), (24, 23, 'Bajaj', 3, 2, 4, 22), (25, 22, 'Automobiles', 6, 1, 7, 22), (26, NULL, 'Art & Antiques', 1, 0, 4, 26), (27, 26, 'Amateur Art', 2, 1, 3, 26); -- -------------------------------------------------------- -- -- Table structure for table `items` -- CREATE TABLE IF NOT EXISTS `items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `category_id` int(11) NOT NULL, `title` varchar(100) NOT NULL, PRIMARY KEY (`id`), KEY `FK_403EA91BA33E2D84` (`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `items` -- INSERT INTO `items` (`id`, `category_id`, `title`) VALUES (1, 24, 'Pulsor 150 cc'), (2, 24, 'Discover 125 cc'), (3, 27, 'Art of dream'), (4, 25, 'Toyota Car'); -- -- Constraints for dumped tables -- -- -- Constraints for table `categories` -- ALTER TABLE `categories` ADD CONSTRAINT `FK_3AF34668727ACA70` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL; -- -- Constraints for table `items` -- ALTER TABLE `items` ADD CONSTRAINT `FK_403EA91BA33E2D84` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE;

root nodes are NULL in the parent_id field

Update:

I managed to get the roots using this query:

 SELECT c.id,c.title,cte.co FROM categories c JOIN (SELECT c0_.id,c0_.root,COUNT(i.id) co FROM categories c0_ JOIN items i ON c0_.id=i.category_id WHERE c0_.rgt = 1 + c0_.lft GROUP BY c0_.id ) cte ON cte.root=c.id WHERE c.parent_id is null

The above query works for the root level category. Now that the user clicks on the root level category, I want to do the same.

for example, when someone clicks on vehicles, I should get:

  Bikes (2) Automobiles (5)

For this, I tried:

  SELECT c.id,c.title,cte.co FROM categories c JOIN (SELECT c0_.id,c0_.root,COUNT(i.id) co FROM categories c0_ JOIN items i ON c0_.id=i.category_id WHERE c0_.rgt = 1 + c0_.lft GROUP BY c0_.id ) cte ON cte.root=c.id WHERE c.parent_id=1

This returns an empty result set. what is wrong with this query?

+6
source share
3 answers
 SELECT parent.title, ( SELECT count(i.id) count FROM items i WHERE category_id IN ( SELECT child.id FROM categories child WHERE child.lft>=parent.lft AND child.rgt<=parent.rgt AND child.root=parent.root ) ) FROM categories parent WHERE parent.parent_id=@parent _id;

Please tell me if this does not work.

+2
source

How about something like this:

 SELECT COUNT(items.id), (SELECT lookup.title FROM categories lookup WHERE lookup.id = categories.root) FROM items, categories WHERE categories.id = items.category_id GROUP BY categories.root;

based on the input from the script above gives me:

 3 | Vehicles 1 | Art & Antiques

to select a specific root add

 AND categories.root = @id

where @id is your root identifier you are looking for.

Alternatively, if you want to select the root name, do something (scary), for example:

 SELECT title, total FROM (SELECT COUNT(items.id) total, (SELECT lookup.title FROM categories lookup WHERE lookup.id = categories.root) title FROM items, categories WHERE categories.id = items.category_id GROUP BY categories.root; ) AS some_table WHERE some_table.title = @root_name

where @root_name is the name of your root node (in quotation marks, of course)

+1
source

Something like this should work.

  select c1.title, count(*) itemcount from categories c1 join categories c2 on c2.parent_id = c1.id join items on items.category_id = c2.id group by c1.title
0
source

More articles:


All Articles