Search code examples
sqlcakephpnested-setscakephp-2.4

CakePHP Tree behavior -find specyfic level elements

I just wondering how do fetch specific level elements from model with TreeBehavior.

It's pretty simply for first level elements.. just find elements with parent_id =>null but for deeper levels...?

Is there any clean and simply way to do that?

Solution

  • This can be done in pure SQL. You can get even the count of descendant per node.

     SELECT n.name,
         COUNT(*)-1 AS level,
         ROUND ((n.rgt - n.lft - 1) / 2) AS offspring
    FROM tree AS n,
         tree AS p
   WHERE n.lft BETWEEN p.lft AND p.rgt
GROUP BY n.lft
ORDER BY n.lft;

    If you add HAVING level = 2 after GROUP the statement, it might get only the level you're looking for. Honestly I've been to lazy to test it right now and to turn it into a CakePHP find() array, give it a try.

    This website http://www.klempert.de/nested_sets/ has a very good explanation of how to work with trees, but it is written in german, you can try to translate it via google translate.