Saving into two tables using multiselect

[shadowx.jb]

I have 3 tables:

language (id, name)
action (id, active)
action_items (action_id, language_id, name)

For tables is foreign keys:

action_items.action_id => action.id
action_items.language_id => language.id

I have form:

<?= $this->Form->create($action) ?>
<?php
    echo $this->Form->control('active');
    echo $this->Form->control('action_items.0.name');
    echo $this->Form->control('action_items.language_id._ids', [
        'type' => 'select',
        'multiple' => true,
        'options' => ['1' => 'CZ', '2' => 'EN']
    ]);
?>
<?= $this->Form->button(__('Submit')) ?>
<?= $this->Form->end() ?>

What I need?

I fill ‘action_items.0.name’
I select ‘action_items.language_id ._ids’ option 1 and 2, after submit form result is:

Result from: $this->request->getData()

[
	'active' => '1',
	'action_items' => [
		(int) 0 => [
			'name' => 'test 4'
		],
		'language_id' => [
			'_ids' => [
				(int) 0 => '1',
				(int) 1 => '2'
			]
		]
	]
]

I would need:

How to save 2 records into the second table action_items if I have selected 2 records from the select?
The patch entity should look like this:

[
	'active' => '1',
	'action_items' => [
		(int) 0 => [
			'name' => 'test 4'
			'language_id' => 1
		],
		(int) 1 => [
			'name' => 'test 4'
			'language_id' => 2
		]
	]
]

If I do patchentity, it looks like this:

Result from: $this->Action->patchEntity($action, $this->request->getData(), [‘associated’ => [‘ActionItems’]]);

object(App\Model\Entity\Action) {

	'active' => (int) 1,
	'action_items' => [
		(int) 0 => object(App\Model\Entity\ActionItems) {

			'name' => 'test 4',
			'[new]' => true,
			'[accessible]' => [
				'language_id' => true,
				'name' => true,
				'action' => true,
				'language' => true
			],
			'[dirty]' => [
				'name' => true
			],
			'[original]' => [],
			'[virtual]' => [],
			'[hasErrors]' => false,
			'[errors]' => [],
			'[invalid]' => [],
			'[repository]' => 'ActionItems'
		
		},
		(int) 1 => object(App\Model\Entity\ActionItems) {

			'[new]' => true,
			'[accessible]' => [
				'language_id' => true,
				'name' => true,
				'action' => true,
				'language' => true
			],
			'[dirty]' => [],
			'[original]' => [],
			'[virtual]' => [],
			'[hasErrors]' => true,
			'[errors]' => [
				'name' => [
					'_required' => 'This field is required'
				]
			],
			'[invalid]' => [],
			'[repository]' => 'ActionItems'
		
		}
	],
	'[new]' => true,
	'[accessible]' => [
		'active' => true,
		'action_items' => true
	],
	'[dirty]' => [
		'aktivni' => true,
		'action_items' => true
	],
	'[original]' => [],
	'[virtual]' => [],
	'[hasErrors]' => true,
	'[errors]' => [],
	'[invalid]' => [],
	'[repository]' => 'Action'

}

Can you help me, please?

[Zuluru]

Your field should not be named action_items.language_id._ids, but action_items.language._ids.

[shadowx.jb]

It does not work…

[Zuluru]

Your original post had a space in the middle of the field name, is that still that? That’ll break it too.

[shadowx.jb]

Yes, you are right, but I wrongly copied it to the forum, in fact there is no gap. I need the output to be as I write at the point: I would need

[Zuluru]

You have non-standard naming that makes it less certain what the right answer here would be. (Cake conventions would normally have tables named “languages” and “actions”, plural, and this has ripple effects).

But I see on closer inspection that you seem to want multiple action items to be created, yet you have only a single name input. How would the set of records that you want to be created from this form look? The same name applied to multiple records created at once? That will require custom handling in your controller, there’s no form layout that will give you that data directly.

[shadowx.jb]

Thank you for your naming warning.

I select 2 languages and store 2 records in the action_items table, including the name that is once in the form. Can this be done without manipulating the controller? If handling is required, how best to do it?

[Zuluru]

If both these records are to have the same name on them, then you need special handling. I’d suggest building your form in such a way that the values are all easily available to your code, but they only populate the Action part of the entity when you patch. Loop over the list of languages selected, creating a new ActionItem entity each time (another naming oddity; entities are generally singular names, but you seem to have plural for this one), with that language ID and the name, and accumulate them in the action_items array on the action entity.

[shadowx.jb]

I have modified the tables to English, for a better understanding, please help with saving. With patchEntity, this action is not performed.

Form only block of selectbox:

echo $this->Form->control('products_accessories_category._ids', [
    'type' => 'select',
    'multiple' => true,
    'options' => $ContentProductsAccessories,
    'escape' => false
]);

Table ProductsItemsTable.php

public function initialize(array $config): void
{
    parent::initialize($config);

    $this->setTable('products_items');
    $this->setDisplayField('id');
    $this->setPrimaryKey('id');

    $this->hasMany('ProductsAccessoriesCategories', [
        'foreignKey' => 'products_item_id',
        'dependent' => true,
        'saveStrategy' => 'replace'
    ]);
}

Table ProductsAccessoriesTable.php

public function initialize(array $config): void
{
    parent::initialize($config);

    $this->setTable('products_accessories');
    $this->setDisplayField('id');
    $this->setPrimaryKey('id');

    $this->hasMany('ProductsAccessoriesCategories', [
        'foreignKey' => 'products_accessory_id',
    ]);
}

Table ProductsAccessoriesCategoriesTable.php - has two primary key!

public function initialize(array $config): void
{
    parent::initialize($config);

    $this->setTable('products_accessories_categories');
    $this->setDisplayField('products_accessory_id');
    $this->setPrimaryKey(['products_accessory_id', 'products_item_id']);

    $this->belongsTo('ProductsAccessories', [
        'foreignKey' => 'products_accessory_id',
        'joinType' => 'INNER',
    ]);
    $this->belongsTo('ProductsItems', [
        'foreignKey' => 'products_item_id',
        'joinType' => 'INNER',
    ]);
}

public function buildRules(RulesChecker $rules): RulesChecker
{
    $rules->add($rules->existsIn(['products_accessory_id'], 'ProductsAccessories'));
    $rules->add($rules->existsIn(['products_item_id'], 'ProductsItems'));

    return $rules;
}

Controller ProductsController.php - save

public function edit($id = null)
{
    $associated = ['ProductsAccessoriesCategories'];

    $Content = $this->ProductsItems->get($id, [
        'contain' => $associated
    ]);

    if ($this->request->is(['patch', 'post', 'put'])) {
        $Content = $this->ProductsItems->patchEntity($Content, $this->request->getData());
        if ($this->ProductsItems->save($Content)) {
            $this->Flash->success('Save ok');

            return $this->redirect(['action' => 'index']);
        }
        $this->Flash->error('Error');
    }

    $this->set(compact('Content'));
}

After patchEntity, if I list the debug variable $Content, I only see data that is read from the database - which is stored. There is no record I select from multiselect. Please advise how to save if I have a link table with two primary keys.

[Zuluru]

I think your form field name maybe needs to be products_accessories_categories._ids. Give that a try.

[shadowx.jb]

if I edit it, after patchEntity the entry is:

products_accessories_categories = []

[Zuluru]

So, a tiny step forward? Safe to assume that you’ve checked the $this->request->getData() to make sure that what you’re expecting is actually in there?

[shadowx.jb]

Yes it is

‘products_accessories_category’ => [
‘_ids’ => [
(int) 0 => ‘1’,
(int) 1 => ‘2’,
(int) 2 => ‘3’
]
]

products_accessories_category because the entity for the ProductsAccessoriesCategoriesTable table is named ProductsAccessoriesCategory

[shadowx.jb]

After the patchEntity, result is:

‘products_accessories_categories’ => [
(int) 0 => object(App\Model\Entity\ProductsAccessoriesCategory) {

  	'products_accessory_id' => (int) 1,
  	'products_item_id' => (int) 2,
  	'[new]' => false,
  	'[accessible]' => [
  		'products_accessory_id' => true,
  		'products_item_id' => true,
  		'products_accessory' => true,
  		'products_item' => true
  	],
  	'[dirty]' => [],
  	'[original]' => [],
  	'[virtual]' => [],
  	'[hasErrors]' => false,
  	'[errors]' => [],
  	'[invalid]' => [],
  	'[repository]' => 'ProductsAccessoriesCategories'
  
  },
  (int) 1 => object(App\Model\Entity\ProductsAccessoriesCategory) {

  	'products_accessory_id' => (int) 2,
  	'products_item_id' => (int) 2,
  	'[new]' => false,
  	'[accessible]' => [
  		'products_accessory_id' => true,
  		'products_item_id' => true,
  		'products_accessory' => true,
  		'products_item' => true
  	],
  	'[dirty]' => [],
  	'[original]' => [],
  	'[virtual]' => [],
  	'[hasErrors]' => false,
  	'[errors]' => [],
  	'[invalid]' => [],
  	'[repository]' => 'ProductsAccessoriesCategories'
  
  },
  (int) 2 => object(App\Model\Entity\ProductsAccessoriesCategory) {

  	'products_accessory_id' => (int) 3,
  	'products_item_id' => (int) 2,
  	'[new]' => false,
  	'[accessible]' => [
  		'products_accessory_id' => true,
  		'products_item_id' => true,
  		'products_accessory' => true,
  		'products_item' => true
  	],
  	'[dirty]' => [],
  	'[original]' => [],
  	'[virtual]' => [],
  	'[hasErrors]' => false,
  	'[errors]' => [],
  	'[invalid]' => [],
  	'[repository]' => 'ProductsAccessoriesCategories'
  
  }

],

but i selected only one item, but show 3 items from stored db… not aplicated my selected items.

[Zuluru]

I suggested that you change the name of your form field to products_accessories_categories._ids. That’s the name of the property in the entity. Looks like you didn’t do that.

[shadowx.jb]

Sorry, i changet to products_accessories_categories._ids

And result is after patch entity is:

object(App\Model\Entity\ProductsItem) {

‘id’ => (int) 2,
‘products_items_categories’ => ,

Item is empty

[Zuluru]

Sorry, it looks from this data like ProductsAccessoriesCategories isn’t the “end” table, but a join table? If that’s the case, then I think that a bunch of your relations may be wrong. Can you explain how the tables involved in this particular operation are related?

[shadowx.jb]

Yes, it is a connecting table, it is used for what goods the accessories are included with.

table: products_items
table: id, name

table: products_accessories
columns: id, name

table: products_accessories_categories
columns: products_accessory_id, products_item_id (two primary key)

products_accessories_categories.products_accessory_id > products_accessories.id
products_accessories_categories.products_item_id > products_items.id

For example:
I have 4 accessories in the table (products_accessories)
For goods, I choose which accessory to assign to the goods (products_accessories_categories).

[Zuluru]

Okay, with this structure, ProductsItems shouldn’t “have many” ProductsAccessoriesCategories. It should “belong to many” ProductsAccessories “through” ProductsAccessoriesCategories. And vice versa in the other direction. And your input field would then be products_accessories._ids.

[shadowx.jb]

If i change, i get error;
‘db.products_accessories_categories_products_items’ doesn’t exist

I print screen table products_accessories_categories