Treepaths

Query

 public static function getTreeByParent($ids)
    {   

        $ids =  (array) $ids;
        $ids = implode($ids, ',');

        // @todo see http://stackoverflow.com/questions/8252323/mysql-closure-table-hierarchical-database-how-to-pull-information-out-in-the-c/8288201#8288201 for visual
        // the whole tree
        // http://karwin.blogspot.be/2010/03/rendering-trees-with-closure-tables.html
        // build query
        $query = 'SELECT d.id, d.child_of, c.name, d.sequence,
                   CONCAT(REPEAT("-- ", p.length), c.name) as hier,
                   p.length, p.ancestor, p.descendant,
                   GROUP_CONCAT(DISTINCT crumbs.ancestor ORDER BY crumbs.ancestor) AS breadcrumbs
                    FROM shop_categories AS d
                    JOIN shop_categories_treepaths AS p ON d.id = p.descendant
                    JOIN shop_categories_treepaths AS crumbs ON crumbs.descendant = p.descendant
                    LEFT JOIN shop_categories_content as c ON c.category_id = d.id
                    WHERE p.ancestor IN (' . $ids . ') AND c.language = ?
                    GROUP BY d.id
                    ORDER BY breadcrumbs';

        // execute the query
        return BackendModel::get('database')->getRecords($query, array(Language::getWorkingLanguage()));
    }

Result

array(6) {
  [0] => array(9) {
    ["id"] => string(1) "2"
    ["child_of"] => NULL
    ["name"] => string(8) "Parent B"
    ["sequence"] => string(1) "2"
    ["hier"] => string(8) "Parent B"
    ["length"] => string(1) "0"
    ["ancestor"] => string(1) "2"
    ["descendant"] => string(1) "2"
    ["breadcrumbs"] => string(1) "2"
  }
  [1] => array(9) {
    ["id"] => string(1) "5"
    ["child_of"] => string(1) "2"
    ["name"] => string(16) "first child of B"
    ["sequence"] => string(1) "2"
    ["hier"] => string(19) "-- first child of B"
    ["length"] => string(1) "1"
    ["ancestor"] => string(1) "2"
    ["descendant"] => string(1) "5"
    ["breadcrumbs"] => string(3) "2,5"
  }
  [2] => array(9) {
    ["id"] => string(1) "6"
    ["child_of"] => string(1) "2"
    ["name"] => string(36) "second child of B but first in order"
    ["sequence"] => string(1) "1"
    ["hier"] => string(39) "-- second child of B but first in order"
    ["length"] => string(1) "1"
    ["ancestor"] => string(1) "2"
    ["descendant"] => string(1) "6"
    ["breadcrumbs"] => string(3) "2,6"
  }
  [3] => array(9) {
    ["id"] => string(2) "20"
    ["child_of"] => NULL
    ["name"] => string(8) "Parent A"
    ["sequence"] => string(1) "1"
    ["hier"] => string(8) "Parent A"
    ["length"] => string(1) "0"
    ["ancestor"] => string(2) "20"
    ["descendant"] => string(2) "20"
    ["breadcrumbs"] => string(2) "20"
  }
  [4] => array(9) {
    ["id"] => string(1) "3"
    ["child_of"] => string(1) "1"
    ["name"] => string(17) "second child of A"
    ["sequence"] => string(1) "2"
    ["hier"] => string(20) "-- second child of A"
    ["length"] => string(1) "1"
    ["ancestor"] => string(2) "20"
    ["descendant"] => string(1) "3"
    ["breadcrumbs"] => string(4) "3,20"
  }
  [5] => array(9) {
    ["id"] => string(1) "4"
    ["child_of"] => string(1) "1"
    ["name"] => string(16) "first child of A"
    ["sequence"] => string(1) "1"
    ["hier"] => string(19) "-- first child of A"
    ["length"] => string(1) "1"
    ["ancestor"] => string(2) "20"
    ["descendant"] => string(1) "4"
    ["breadcrumbs"] => string(4) "4,20"
  }
}