[eluser]markup2go[/eluser]
I have 4 tables: Sections, Categories, Subcategories, and Products. I'm simply trying to query all the sections, and for each sections count the number of categories, subcategories, and products for that section. The only table I have a section_id field is in Categories.
So what I really need to do is:
1. Get all sections
2. Count all categories with section_id = to section's id
3. Foreach result of #2 count subcategories that contain category_id = to #2 results
4. Then count all products that contain the subcategory_id...
Here is what I'm trying to do in CI:
Code:
$this->db->select('product_sections.*, COUNT(product_categories.id) as totalCats, COUNT(product_subcategories.id) as totalSubcats, COUNT(products.id) as totalProducts');
$this->db->join('product_categories', 'product_sections.id = product_categories.section_id', 'left');
$this->db->join('product_subcategories', 'product_subcategories.category_id = product_categories.id', 'left');
$this->db->join('products', 'products.subcategory_id = product_subcategories.id', 'left');
$this->db->group_by('product_sections.id');
$this->db->order_by('product_sections.name', 'asc');
$query = $this->db->get('product_sections');
This obviously don't return the right numbers. It works fine with just 1 join (I can get the total number of categories) however I am getting some random number of counts using my method.
Can someone please please please post an example of how to do this using my table scheme?
[code]
CREATE TABLE IF NOT EXISTS `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subcategory_id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`price` float(10,2) DEFAULT NULL,
`shipping_price` float(10,2) DEFAULT NULL,
`photo` varchar(255) DEFAULT NULL,
`description` text,
PRIMARY KEY (`id`),
KEY `products_ibfk_1` (`subcategory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
-- --------------------------------------------------------
--
-- Table structure for table `product_categories`
--
CREATE TABLE IF NOT EXISTS `product_categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`section_id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `product_categories_ibfk_1` (`section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=39 ;
-- --------------------------------------------------------
--
-- Table structure for table `product_sections`
--
CREATE TABLE IF NOT EXISTS `product_sections` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;
-- --------------------------------------------------------
--
-- Table structure for table `product_subcategories`
--
CREATE TABLE IF NOT EXISTS `product_subcategories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `product_subcategories_ibfk_1` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=43 ;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `products`
--
ALTER TABLE `products`
ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`subcategory_id`) REFERENCES `product_subcategories` (`id`) ON DELETE CASCADE;
--
-- Constraints for table `product_categories`
--
ALTER TABLE `product_categories`
ADD CONSTRAINT `product_categories_ibfk_1` FOREIGN KEY (`section_id`) REFERENCES `product_sections` (`id`) ON DELETE CASCADE;
--
-- Constraints for table `product_subcategories`
--
ALTER TABLE `product_subcategories`
ADD CONSTRAINT `product_subcategories_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`) ON DELETE CASCADE;