We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

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.



1.6k

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



14.1k

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!



1.6k
in reply to Mateusz Błaszczyk

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!



93.7k

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 :)



6.6k
in reply to Mateusz Błaszczyk

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!