Get the count of items related to nested category in sql

Question

I have two tables: categories and items. i have stored categories using nested set structure. Categories have items. Items can be only added to leaf nodes of a root category.

For eg: Categories

Vehicles
    Bikes
        Bajaj
    Automobiles
Art & Antiques
    Amateur Art

Items can be added to category Bajaj, Automobiles and Amateur Art in this case.

Lets say there are 2 items inside Bajaj, 5 items inside Automobiles, 2 inside Amateur Art

For root level categories I want to display as follow:

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

How can I do this ?

Here is the sql dump to work 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 have NULL in the field of parent_id

Update:

I was able to fetch for 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 root level category. Now when the user clicks on root level category, I want to do the same.

for eg when somebody clicks on vehicles I should get:

 Bikes (2)
 Automobiles (5)

For that 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 returned empty result set. what is wrong in this query ?

Answer 1

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 inform me if this does not work

Answer 2

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 for a particular root add

AND categories.root = @id

where @id is your root id you're looking for.

Alternatively if you want to select by the root name do something (scary) like this:

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 quotes of course)