How to select multiple counts from multiple tables

[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;

[eluser]jedd[/eluser]
Can you post what CI did to that query (ie. what it actually ended up translating that query into).

It's possible you can get where you want to go by using the second param (set to FALSE) in your ->db->select function.

[eluser]markup2go[/eluser]
Hi jedd,

I've added FALSE to the second parameter and here is what the query ends up looking like:

SELECT product_sections.*, COUNT(product_categories.id) as totalCats, COUNT(product_subcategories.id) as totalSubcats, COUNT(products.id) as totalProducts
FROM (product_sections)
LEFT JOIN product_categories ON product_sections.id = product_categories.section_id
LEFT JOIN product_subcategories ON product_subcategories.category_id = product_categories.id
LEFT JOIN products ON products.subcategory_id = product_subcategories.id
GROUP BY product_sections.id
ORDER BY product_sections.name asc

[eluser]jedd[/eluser]
When you say 'random results' - have you analysed the numbers in your various Cats results to see what they actually correlate to in your test data?

Are your total*'s disregarding the left joins, for example.

Btw, do you really need product_ in front of all the table names?

[eluser]markup2go[/eluser]
Random results as in the counts aren't the correct counts, at all.

No I don't really need product_ in front, but that's another subject and I did it for organizational purposes.

[eluser]jedd[/eluser]
Yes, I understand that you're using the word random when you mean wrong.

My point is that unless you get *different* results every time you run that query, then the results aren't actually random. By looking at what the results are showing you - ie. by looking for patterns in your test data - you can work out what's going wrong with your query. In other words, I'm suggesting a way of identifying the problem.

Alternatively you can just wait for someone really smart to come along and identify the problem.

[eluser]markup2go[/eluser]
Gotcha.

[eluser]kurucu[/eluser]
Two observations, but they may not be the (only) reasons for the problem:

You are only grouping by sections.id, when there is a sections.name that could be causing the result list to expand. In case this makes no sense to you, here is my rationale:
- You are selecting * from sections
- You are using grouping functions for other tables
- You are grouping only one of the two fields in sections

Second, you have selected left. As far as I know, you need to count all results found in tables other than sections for your count to be accurate. So, you need a normal join, not a left one.

I'd suggest trying one of these two solutions:
- change the joins on sections to be standard and not left joins (although I don't have confidence in this)
And/Or
- perform a grouping functions for sections.name / sections.id (such as MAX()) OR add a group_by for sections.name

It might be handy for you to just dump the results as a table (e.g. drop that query into PHPMyAdmin) and see what's going on.

[eluser]markup2go[/eluser]
I figured it out, I think. Please correct me if I'm doing something wrong.

Code:

$this->db->select('product_sections.*, COUNT(DISTINCT(c.id)) as totalCats, COUNT(DISTINCT(s.id)) as totalSubcats, COUNT(DISTINCT(p.id)) as totalProducts', FALSE);
$this->db->join('product_categories as c', 'product_sections.id = c.section_id', 'left');    
$this->db->join('product_subcategories as s', 's.category_id = c.id', 'left');    
$this->db->join('products as p', 'p.subcategory_id = s.id', 'left');        
$this->db->group_by('product_sections.id');
$this->db->order_by('product_sections.name', 'asc');

And it produces this query:
SELECT product_sections.*, COUNT(DISTINCT(c.id)) as totalCats, COUNT(DISTINCT(s.id)) as totalSubcats, COUNT(DISTINCT(p.id)) as totalProducts
FROM (product_sections)
LEFT JOIN product_categories as c ON product_sections.id = c.section_id
LEFT JOIN product_subcategories as s ON s.category_id = c.id
LEFT JOIN products as p ON p.subcategory_id = s.id
GROUP BY product_sections.id
ORDER BY product_sections.name asc