CODEWITHSUNDEEP
phpmysqlrecursion
Rupesh Pawar
Rupesh Pawar

Reputation: 1917

Get a leaf nodes by recursive query

I have a categories structure as follows

enter image description here

I want to select leaf node. i mean the the categories who don't have sub categories.

In my database monitor, cpu, novels and comics will be answer.

Any help will be appreciated.

Edit : I tried this.

public function get_valid_categories($parent)
        {
                $has_childs = false;
                foreach($this->categories as $key => $value) {
                    if ($value['parent'] == $parent) {
                        if ($has_childs === false) {
                            $has_childs = true;
                        }
                        else
                        {
                            $this->valid_categories[] =  $value['name'];
                        }
                        $this->get_valid_categories($key);

                    }
                }
                if ($has_childs === true)
                {
                    return $this->valid_categories ;
                }
        }

and i m calling this function as follows

get_valid_categories(0);

Upvotes: 1

Views: 1427

Answers (2)

marcio
marcio

Reputation: 10512

Well, there are probably many possible solutions but here is the first one that came to my mind:

SELECT *
FROM categories
WHERE id NOT IN (
                 SELECT DISTINCT(parent_id) FROM categories
                )

Not so elegant as using joins, but can be an alternative solution for the problem. Hope that helps.

Upvotes: 2

a'r
a'r

Reputation: 36999

You don't need a recursive query for this. How about the following SQL:

select t1.* 
from table t1
    left join table t2 on t1.id = t2.parent_id
where t2.id is null

This takes the rows in your table and self-joins to get each row's children. It then filters out those rows that have no children by checking for t2.id is null.

Upvotes: 4

Related Questions