Databases
StackOverflow2013
Postsdbo
POMost efficient way to count items under a nested tree
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMost efficient way to count items under a nested tree
    text
    copied!<p>So I'm looking to provide a <code>category (x)</code> style product count on my website.</p> <p>I'm currently using MySQL. </p> <p>My categories table looks like: <code>Categories (Id, TreeLeft, TreeRight, Level, Name)</code> - with 'Level' being the node depth.</p> <p>Categories entered look like this: <img src="https://i.stack.imgur.com/f0UaV.png" alt="enter image description here"></p> <p>My Item > Categories relation table looks like: <code>ItemCategories (ItemId, CategoryId)</code></p> <p>Assuming I have:</p> <ul> <li>1 item under 'TUBE'</li> <li>2 items under 'LCD'</li> <li>1 item under 'FLASH'</li> <li>1 item under '2 WAY-RADIOs'</li> </ul> <p>How can I most efficiently query my items (large db) + categories (4000 in db), to produce:</p> <pre> Electronics (5) - Televisions (3) - Tube (1) - LCD (2) - Portable Electronics (2) - MP3 Players (1) - Flash (1) - 2 Way Radios (1) </pre> <p>Taking note to only return those categories which have products in them and also correctly counts them up the tree.</p> <p>Any help most appreciated.</p> <p>Edit: DB Code to recreate environment locally:</p> <pre> CREATE TABLE IF NOT EXISTS `Categories` ( `Id` int(11) NOT NULL auto_increment, `TreeLeft` mediumint(7) NOT NULL, `TreeRight` mediumint(7) NOT NULL, `Level` tinyint(3) NOT NULL, `Name` varchar(255) NOT NULL, UNIQUE KEY `Id` (`Id`), KEY `TreeLeft` (`TreeLeft`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; INSERT INTO `Categories` (`Id`, `TreeLeft`, `TreeRight`, `Level`, `Name`) VALUES (1, 1, 20, 1, 'Electronics'), (2, 2, 9, 2, 'Television'), (3, 10, 19, 2, 'Portable Electronics'), (4, 3, 4, 3, 'Tube'), (5, 5, 6, 3, 'LCD'), (6, 7, 8, 3, 'Plasma'), (7, 11, 14, 3, 'MP3 Players'), (8, 15, 16, 3, 'CD Players'), (9, 11, 14, 3, '2 Way Radios'), (10, 12, 13, 4, 'Flash'); CREATE TABLE IF NOT EXISTS `ItemCategories` ( `CategoryId` int(11) NOT NULL, `ItemId` int(11) NOT NULL, KEY `CategoryId` (`CategoryId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `ItemCategories` (`CategoryId`, `ItemId`) VALUES (4, 3442), (5, 3441), (5, 3456), (9, 5343), (10, 5423); </pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload