Sometimes it is useful to show in a list the numbers of children each object has. For instance how many Products are in each Category.
That’s simple enough to do when only one child type is used:
SELECT categories.title, categories.id, count(products.id) as productCount FROM categories INNER JOIN products ON categories.id = products.categoryid
However, if you wanted to count two separate children types, the same method of querying will give you very odd results:
SELECT categories.title, categories.id, count(products.id) as productCount, count(images.id) as imagesCount FROM categories INNER JOIN products ON categories.id = products.categoryid INNER JOIN images ON categories.id = images.categoryid
The lazy programmer would get around this by using the server language – looping through a simple query of the category, and with each row run another query to count the products (and another for the images). Yuck.
Instead, I found this method to work quite well:
SELECT cats.title AS title, cats.ID AS id, COALESCE(prods.howmany,0) AS productCount, COALESCE(imgs.howmany,0) AS imageCount FROM ( SELECT title, ID FROM categories ) AS cats LEFT JOIN ( SELECT count(products.id) as howmany, products.categoryid as categoryid FROM products LEFT JOIN categories ON products.categoryid = categories.id GROUP BY products.categoryid ) AS prods ON cats.id=prods.categoryid LEFT JOIN ( SELECT count(images.id) as howmany, images.categoryid as categoryid FROM images LEFT JOIN categories ON images.categoryid = categories.id GROUP BY images.categoryid ) AS imgs ON cats.id=imgs.categoryid
Note the use of the ‘COALESCE()’ function so that nulls are returned as a 0 (zero).
As you can see the database is still running three queries. But they are all within one connection, and the server language didn’t do any work. Then MySQL joins those queries together into one result, easily parsed by your server language of choice. Here, ColdFusion:
<table> <tr> <td>Category</td> <td>Products</td> <td>Images</td> </tr> <cfoutput query="read"> <tr> <td>#title#</td> <td>#productCount#</td> <td>#imageCount#</td> </tr> </cfoutput> </table>