MySQL count child elements

Question

I have a categories table:

id | name |  parent_id
1  | Camaro | 0
2  | Chevelle | 0
3  | Sale - Camaro Parts | 1
4  | Bestselling Parts | 1

My first request looks like:

'SELECT 
    * 
 FROM 
    `categories`
 WHERE
    parent_id = :parent_id';

And after I'm fetching result set I make sub query to check if row has child elements:

foreach($result as $r) {
    $r->hasChild    = count(ORM::forTable('categories')->where('parent_id', $r->id)->findArray());
    $data[]         = $r;
}

Is any way to avoid multiple connection to DB in foreach loop and get data in first query?

Thanks!

`SELECT count(*) FROM `categories` WHERE parent_id = :parent_id;` — Harshil Doshi, Oct 26 '17 at 18:26
Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Juan Carlos Oropeza, Oct 26 '17 at 18:28

score 2 · Accepted Answer · answered Oct 26 '17 at 18:29

This isn't awful to do, so long as you only want the count of children below the selected rows. If you want the entire hierarchy, you'll need to use a better RDMS.

The main part of the solution here is self joining the same table. Then we can use the count() aggregate function to see how many children are attached to each item.

select
  categories.id
  , categories.name
  , categories.parent_id
  , count(chld.id)
from
  categories
  left join categories chld
    on categories.id = chld.parent_id
where
  parent_id = :parent_id
group by
  categories.id
  , categories.name
  , categories.parent_id

score 0 · Answer 2 · answered Oct 26 '17 at 18:28

You can do a self join to the table on parent_id and id. Based on whether you want categories with child or not you can do a left join or inner join. Kind of a similar question is mentioned here - Mysql Self Join to find a parent child relationship in the same table

MySQL count child elements

2 Answers2