Get all related records (children and grandchildren)

Hello! At the begening I apologize for my English that is on th weak level but I wish You can understand my question. I have table:

CREATE TABLE IF NOT EXISTS `ShopCategory` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `parentID` int(11) unsigned DEFAULT NULL,
  `description` text,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `parentID` (`parentID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Model:

    public function initialize()
    {
        $this->setSource("ShopCategory");
        $this->hasMany('ID', 'ShopCategory', 'parentID', array('alias' => 'categories'));
        $this->belongsTo('parentID', 'ShopCategory', 'ID', array('alias' => 'ShopCategory'));
    }

I need to get all records in format:

<ul>
    <li>processors
        <ul>
            <li>AMD</li>
            <li>Intel
                <ul>
                    <li>i5</li>
                    <li>i7</li>
                </ul>
            </li>
        </ul>
    </li>
</ul>

Thats all are categories (Processors, ADM, Intel, i5, i7);

I tried do all day but i can't write function to do that. I'll be very grateful for help.

query build from a new public method on your model, or use PHQL to outer left join

I recommend You nested NestedSet from: https://github.com/phalcon/incubator/tree/master/Library/Phalcon/Mvc/Model/Behavior. I spent whole day to implement it in my applicaiton, but it works really great. You should use it!

I'm not sure, but it seems like the relationship is only one-level deep... I like what the solution you propose does if someone absolutely has to have an n-depth tree structure, but I often find that dealing with any structures at a max of 3 levels is optimal for speed, and cognition of the underlying system. (let's be honest, most people don't cognitively process incredibly complex systems, they focus on parts of them)

I recommend You nested NestedSet from: https://github.com/phalcon/incubator/tree/master/Library/Phalcon/Mvc/Model/Behavior. I spent whole day to implement it in my applicaiton, but it works really great. You should use it!

Hello friend,

I have a little function which i used in many projects so far:

    public function getTree()
    {
        $tree = [];
        $nodeList = [];

        $builder = $this->modelsManager->createBuilder();
        $builder->columns(['main.*', 'main18.*']);
        $builder->from(['main' => 'Models\Categories']);
        $builder->leftJoin('Models\CategoriesI18n', 'main18.foreign_key = main.id', 'main18');
        $builder->where('main.is_active = "1"');
        $builder->andWhere('main18.lang = :lang:', ['lang' => $this->getDI()->getSession()->language]);
        $items = $builder->getQuery()->execute();

        foreach ($items as $item) {
            $tmp = [
                'id' => $item->main->id,
                'title' => $item->main18->title,
                'slug' => $item->main18->slug
                'parent_id' => $item->main->parent_id,
            ];
            $nodeList[$item->main->id] = array_merge($tmp, ['sub' => []]);
        }

        foreach ($nodeList as $nodeId => &$node) {
            if (!$node['parent_id'] || !array_key_exists($node['parent_id'], $nodeList)) {
                $tree[$node['id']] = &$node;
            } else {
                $nodeList[$node['parent_id']]['sub'][$node['id']] = &$node;
            }
        }
        return $tree;
    }

You can modify it to fit your needs :)



2.1k

Have you implement Phalcon 3, PHP7 with incubator nested set behaviour ?

I can not create new root to database Can you help me please

I recommend You nested NestedSet from: https://github.com/phalcon/incubator/tree/master/Library/Phalcon/Mvc/Model/Behavior. I spent whole day to implement it in my applicaiton, but it works really great. You should use it!