Problems with dynamic drop-down lists in cakephp 3

Question

Friends, I have 3 tables: products, categories and subcategories. I am trying to register products and through two dependent drop-down lists on the form (categories and subcategories). That is, when selecting the category, the subcategories are loaded in the other list, but I'm having difficulties with that even consulting the documentation! I am grateful if anyone can point a way! I'll summarize the fields!

Here is my table:

---

products: id, name, category_id, subcategory_id

categories: id, name

subcategories: id, name, category_id

---

I'm registering the new product like this:

 public function add()
    {
        $product = $this->Products->newEntity();
        if ($this->request->is('post')) {
            $product = $this->Products->patchEntity($product, $this->request->getData());
            if ($this->Products->save($product)) {
                $this->Flash->success(__('The produto has been saved.'));
                return $this->redirect('/admin/products/');
            }
            $this->Flash->error(__('The produto could not be saved. Please, try again.'));
        }
        $categories = $this->Products->Categories->find('all')->contain(['Subcategories']);    
        $subcategories = $this->Products->Subcategories->find('list', ['limit' => 200]);
        $this->set(compact('product', 'categories', 'subcategories'));
    }

ProductTable.php

.
.
$this->belongsTo('Categories', [
            'foreignKey' => 'category_id',
            'joinType' => 'INNER',
        ]);
        $this->belongsTo('Subcategories', [
            'foreignKey' => 'subcategory_id',
            'joinType' => 'INNER',
        ]);
.
.

CategoriesTable.php

.
$this->hasMany('Product', [
      'foreignKey' => 'category_id',
]);
.

SubcategoriesTable.php

.
 $this->belongsTo('Categories', [
            'foreignKey' => 'category_id',
            'joinType' => 'INNER',
]);
$this->hasMany('Product', [
            'foreignKey' => 'subcategory_id',
]);
.

Product => add.ctp

<php
$categories_list = [];
$subcategories_list = [];

foreach($categories as $category){

   $categories_list[$category->id] = $category->name; 

   foreach($category->subcategories as $subcategory){
     $subcategories_list[$category->id][$subcategory->id] = $subcategory->name;
   }
}

?>


<div class="produtos form large-9 medium-8 columns content">
    <?= $this->Form->create($product, ['class' => 'ajax_page']) ?>
    <fieldset>
        <legend><?= __('Add Produto') ?></legend>
        <?php

          echo $this->Form->select('category_id', ['options'=>$categories_list,'id'=>'category']);

         echo $this->Form->select('subcategory_id', ['options'=>[], 'id'=>'subcategory']);
?>

  </fieldset>
    <?= $this->Form->button(__('Submit')) ?>
    <?= $this->Form->end() ?>
</div>

<script>

var subCategories = <?= json_encode($subcategories_list);  ?>;

$(document).ready(function(){
         $('#category').change(function(){

  var categoryId = $(this).val();

  var subCategoriesObject = subCategories[categoryId];

  $('#subcategory option:gt(0)').remove();
  var subCategoriesSelect = $('#subcategory');

  $.each(subCategoriesObject, function(key,value) {
     subCategoriesSelect.append($("<option></option>").attr("value", key).text(value));
 });

});
});

</script>

---

With this update, there is no error, but it does not load the name of the subcategories.

Category:

Subcategory:

I did a test this way too, but the subcategory doesn't show any value:

<script>
    $(document).ready(function () {
        var subCategories = {
            '0': [
                'cat0.0', 'cat0.1', 'cat0.2',
            ],
            '1': [
                'cat1.0', 'cat1.1', 'cat1.2',
            ],
            '2': [
                'cat2.0', 'cat2.1', 'cat2.2',
            ],
        };
        $('#category').change(function () {

            var categoryId = $(this).val();
            var subCategoriesObject = subCategories[categoryId];

            $('#subcategory option:gt(0)').remove();
            var subCategoriesSelect = $('#subcategory');

            $.each(subCategoriesObject, function (key, value) {
                subCategoriesSelect.append($("<option></option>").attr("value", key).text(value));
            });

        });
    });

</script>

Log JS:

I appreciate any comment or example!

Answer 1

You don't have to fetch as list, you can work your way trough by just fetching categories with its subcategories and then build your HTML/JS from there.

Like this:

$categories = $this->Products->Categories->find('all')
->contain(['Subcategories']);

in HTML/CTP:

<php
$categories_list = [];
$subcategories_list = [];

foreach($categories as $category){

   $categories_list[$category->id] = $category->name; 

   foreach($category->subcategories as $subcategory){
     $subcategories_list[$category->id][$subcategory->id] = $subcategory->name;
   }
}

?>

So you have now an array with all subcategories and the category id is used as a key.

<?php

echo $this->Form->select('category_id', ['options'=>$category_list, 'id'=>'category']);

echo $this->Form->select('subcategory_id', ['options'=>[], 'empty'=>'...', 'id'=>'subcategory']);
?>

Now Js

<script>

var subCategories = <?= json_encode($subcategories_list);  ?>;

$(document).ready(function(){
         $('#category').change(function(){

  var categoryId = $(this).val();

  var subCategoriesObject = subCategories[categoryId];

  $('#subcategory option:gt(0)').remove();
  var subCategoriesSelect = $('#subcategory');

  $.each(subCategoriesObject, function(key,value) {
     subCategoriesSelect.append($("<option></option>").attr("value", key).text(value));
 });

});
});

</script>

I haven't tested the code or anything but I have done this multiple times, you get the idea.