How to use find threaded

Need Help
1

How to link two tables so that I can get the main nodes and their child nodes with find threaded?
My target is to render a menu like this

<ul>
    <li class="category">Category 1</li>
       <ul>
          <li class="sub">Subcategory 1</li>
          ...
       </ul>
    <li class="category">Category 2</li>
        <ul>
            <li class="sub">Subcategory 1</li>
            ...
        </ul>
   </li>
</ul>

I have created two tables.
Categories

CREATE TABLE `categories` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `categories` (`id`, `name`) VALUES (NULL, 'Category 1');
INSERT INTO `categories` (`id`, `name`) VALUES (NULL, 'Category 2');

Subcategories

CREATE TABLE `subcategories` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    `category_id` INT UNSIGNED NULL DEFAULT NULL
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `subcategories` (`id`, `name`, `category_id`) VALUES (NULL, 'Subcategory 1', 1);
INSERT INTO `subcategories` (`id`, `name`, `category_id`) VALUES (NULL, 'Subcategory 1', 1);
INSERT INTO `subcategories` (`id`, `name`, `category_id`) VALUES (NULL, 'Subcategory 1', 1);
INSERT INTO `subcategories` (`id`, `name`, `category_id`) VALUES (NULL, 'Subcategory 1', 2);
INSERT INTO `subcategories` (`id`, `name`, `category_id`) VALUES (NULL, 'Subcategory 1', 2);

My association for these two tables look like

class CategoriesTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsTo('Subcategories', [
            'foreignKey' => 'category_id'
        ]);
    }
   ...
}

class SubcategoriesTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsTo('Categories');
    }
    ...
}

When i now output the Result of

TableRegistry::getTableLocator()->get('Categories')->find('threaded')->toArray();

I get the list of Categories but the children nodes are empty as of missing of parent_id.
According to the definition of CakePHP you should use a parent_id for this. But I am not clear how to use this.
https://book.cakephp.org/3/en/orm/retrieving-data-and-resultsets.html#finding-threaded-data

I am still CakePHP beginner and maybe somebody can help me with this.

2

I think that what you want is not find('threaded'), but rather find()->contain(['Subcategories']). Threaded is, as far as I can tell, for when the related records are all in the same table, e.g. a post has replies which are also posts, which in turn have replies which are also posts, etc.

3

i have modified my Table Structures to

CREATE TABLE `categories` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    `parent_id` INTEGER(10) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `categories` (`id`, `name`, `parent_id`) VALUES (NULL, 'Category 1', NULL);
INSERT INTO `categories` (`id`, `name`, `parent_id`) VALUES (NULL, 'Category 2', NULL);

CREATE TABLE `subcategories` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    `parent_id` INTEGER(10) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `subcategories` (`id`, `name`, `parent_id`) VALUES (NULL, 'Subcategory 1', 1);
INSERT INTO `subcategories` (`id`, `name`, `parent_id`) VALUES (NULL, 'Subcategory 1', 1);
INSERT INTO `subcategories` (`id`, `name`, `parent_id`) VALUES (NULL, 'Subcategory 1', 1);
INSERT INTO `subcategories` (`id`, `name`, `parent_id`) VALUES (NULL, 'Subcategory 1', 2);
INSERT INTO `subcategories` (`id`, `name`, `parent_id`) VALUES (NULL, 'Subcategory 1', 2);

CategoriesTable - Association

$this->hasMany('Subcategories', [
     'foreignKey' => 'parent_id'
]);

SubcategoriesTable - Association

$this->belongsTo('Categories');

At Controller i can call

TableRegistry::getTableLocator()
               ->get('Categories')
               ->find('threaded')
               ->contain(['Subcategories'])
               ->toArray();

and i get “Categories” and it’s related “Subcategories”.
But the Subcategories will stay within “subcategories” node not with in “children” node

I need to store Subcategories in an extra Table as it is used on other Places in the App

So how to get results into “children” node or does “threaded” not work with associated Tables as @Zuluru mentioned

4

Categories hasMany Subcategories is a perfectly fine structure. Your original database setup was all fine for working with that. The only changes you needed to make were the change to the find call that I mentioned, eliminating threaded and using contain instead, and I also now see that the association from Categories to Subcategories should have been hasMany there instead of belongsTo (though belongsTo is right for the association from Subcategories to Categories).

If you insist on the list of subcategories that gets loaded into the category children instead of the default subcategories, you can use the propertyName parameter when adding the association.

Putting it all together:

CREATE TABLE `categories` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `subcategories` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    `category_id` INT UNSIGNED NULL DEFAULT NULL
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

class CategoriesTable extends Table
{
    public function initialize(array $config)
    {
        $this->hasMany('Subcategories', [
            'foreignKey' => 'category_id',
            'propertyName' => 'children', // Though I recommend against this and just using the default
        ]);
    }
   ...
}

class SubcategoriesTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsTo('Categories');
    }
    ...
}

TableRegistry::getTableLocator()->get('Categories')->find()->contain(['Subcategories'])->toArray();
1 Like
5

@Zuluru thanks for your feedback. I will stay with the Solution using find without threaded and not overriding the “propertyName”. Thanks for this advice!
As it is a very simple nesting in my case.