BelongsToMany Example

I’m using 3.9.5 to develop a new app. Need to use a BelongsToMany association in this application that is very similar to the Articles/Tags association in the CMS Example in the documentation.

I have the table structure setup (with the association table) along with the controllers and templates. Having a problem getting it to work. The association table never populates.

I have combed through the CMS example, the GitHub CMS content, tried Baking it, and reviewed the Associations documentation but cannot figure out what I am doing wrong. Does anyone know of a good working BTM example I can reference?

Thanks

My open source project has lots of examples of this.

Thanks for the quick reply. There is a lot out there. I was going to start at the tables and work my way up through the example. Do you have a recommendation for which set of tables I should look at first?

The simplest belongsToMany is maybe divisions to days, or teams to franchises?

Been reviewing the examples and looking at my code. Can’t find what I am missing. Maybe another set of eyes on it will help point me in the right direction. Here are the code snippets. To simplify, there is a users table and a units (aka departments) table.

Users Table

class UsersTable extends Table
{
public function initialize(array $config)
{
parent::initialize($config);

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

    $this->belongsToMany('Units', [
        'joinTable' => 'units_users',
        'foreignKey' => 'user_id',
        'targetForeignKey' => 'unit_id',
		'saveStrategy' => 'replace',
		'dependent' => true
    ]);

Units Table

class UnitsTable extends Table
{
public function initialize(array $config)
{
parent::initialize($config);

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

    $this->belongsTo('UnitTypes', [
        'foreignKey' => 'unit_type_id',
        'joinType' => 'INNER',
    ]);
    $this->hasMany('Beds', [
        'foreignKey' => 'unit_id',
    ]);
    $this->belongsToMany('Users', [
        'joinTable' => 'units_users',
        'foreignKey' => 'unit_id',
        'targetForeignKey' => 'user_id',
		'saveStrategy' => 'replace',
		'dependent' => true
    ]);

Units_Users Table

class UnitsUsersTable extends Table
{
public function initialize(array $config)
{
parent::initialize($config);

    $this->setTable('units_users');
    $this->setPrimaryKey('unit_id', 'user_id');

    $this->belongsTo('Units', [
        'foreignKey' => 'unit_id',
		'joinType' => 'INNER'
    ]);

    $this->belongsTo('Users', [
        'foreignKey' => 'user_id',
		'joinType' => 'INNER'
    ]);

User Controller

public function add()
{
$user = $this->Users->newEntity();

if ($this->request->is('post')) {
$user = $this->Users->patchEntity($user, $this->request->getData());
    if ($this->Users->save($user)) {
        $this->Flash->success(__('The user has been saved.'));

        return $this->redirect(['action' => 'index']);
}
    $this->Flash->error(__('The user could not be saved. Please, try again.'));
}

$units = $this->Users->Units->find('list');

$this->set(compact('user', 'units'));

}

User Add Template

<?= $this->Form->create($user) ?>


echo $this->Form->control(‘name’);
echo $this->Form->control(‘username’);
echo $this->Form->control(‘password’);
echo $this->Form->control(‘units._ids’, [‘options’ => $units]);
echo $this->Form->control(‘email’);

When a user is added, the Users table is populated correctly. The Units_Users table is not updated. What am I missing?

Have you tried

$user = $this->Users->patchEntity($user, $this->request->getData(), ['associated' => ['Units']]);

I had tried adding it in the past and just tried it again. No luck. Still does not add the record.

I did some additional testing by adding the user through the app and then manually adding a join record in the Units_Users table. When deleting the user, it deletes the join record as expected. When I setup the same test and do an edit(), it properly pulls the association and displays it. When I “submit” the form, it deletes the join record as expected but does not re-add it.

Looks like the problem is strictly with the adding of the join record.

It’s nothing silly like you neglected to clear the ORM cache after making some change to the schema?

I am not always strict at clearing the cache but I did try clearing it and tested again with the same result.

Is there a way I can confirm it is loading the Entity files? I came across this bug report:

It sounds awfully familiar.

Yeah, give us the result of a debug($user); after patching it.

Looks like it is definitely loading the User entity file. I’m more concerned with loading the UnitsUser entity file for the add. I don’t think it is or maybe it’s not supposed to because the unit array is blank. I placed a bogus function call in the file that I know would throw an error. When I did the add, no error. When I went back to delete the test record, I got the error I was expecting. evidently, it is being loaded on the delete but not the add. Also, units is being returned from the User Add template blank. That is a problem. The template uses:

echo $this->Form->control(‘units._ids’, [‘options’ => $units]);

And the controller retrieves it:

$user = $this->Users->patchEntity($user, $this->request->getData(), [‘associated’ => [‘Units’]]);

It presents the data properly in the edit template when I manually add the record in the join file.

Here is $user:

object(App\Model\Entity\User) {

'name' => 'test',
'username' => 'Testname',
'password' => '$2y$10$gQcRoTD9H1OdSWb4FczSCu8kccVnS90Kgdz/HEFv3J7dT.qcfX.9u',
'role' => 'M',
'units' => [],
'email' => 'test@aaaa.com',
'[new]' => true,
'[accessible]' => [
	'name' => true,
	'username' => true,
	'password' => true,
	'role' => true,
	'email' => true,
	'passkey' => true,
	'timeout' => true,
	'units' => true
],
'[dirty]' => [
	'name' => true,
	'username' => true,
	'password' => true,
	'role' => true,
	'units' => true,
	'email' => true
],
'[original]' => [],
'[virtual]' => [],
'[hasErrors]' => false,
'[errors]' => [],
'[invalid]' => [],
'[repository]' => 'Users'

}

I don’t know if a UnitsUser entity would be created by the patch; your $user->units should just be an array of Unit entities. It is accessible, and it is dirty, so it seems that it’s doing something with it. Does $units have the right format? What does $this->request->getData() look like?

I think you are right. However, the UnitsUser should come into play at the save(). That’s where is should be writing the user and unitsusers records. I am assuming that units is not initiating any database activity because there is no data even through it is flagged as dirty.

This is looking like the BTM may actually be working but the data is not making it to the CRUD operations.

Grabbed the getData() to see the POST data. Interesting:

[
‘name’ => ‘test’,
‘username’ => ‘Testname’,
‘password’ => ‘secret’,
‘role’ => ‘M’,
‘units’ => [
‘_ids’ => ‘2’
],
‘email’ => ‘aasd@asd.com’
]

Looks like it is sending back the selected unit ID in the POST but the patch doesn’t seem to be handling it. I would expect to see the same unit data in the array after the patch and then sent for marshaling during the save().

Ah! Well, _ids should be an array for the patch, not just a single value. If it’s just a single value, then maybe the field name should be units.0.id instead.

Now that we are narrowing down the problem area, I put back some of my original code to replace the BTM example code that I was using.

In the controller, I put back the Unit find code:

// $units = $this->Users->Units->find(‘list’);
$units = $this->Users->Units->find(‘list’, [
‘keyFields’ => ‘id’,
‘valueField’ => ‘description’,
‘order’ => array(‘Units.description’ => ‘asc’),
‘limit’ => 200
]);

In the template, I change back how the field is presented. There is some code in there to enable/disable the multi select field based on the role field but that is not really the issue. Here is the change:

// echo $this->Form->control(‘units._ids’, [‘options’ => $units]);
echo $this->Form->control(‘units._id’, [
‘options’ => $units,
‘label’ => false,
‘multiple’ => true,
‘id’ => ‘upick’,
[‘disabled’]
]);

I am back to having the unit descriptions rather than the id and I can select multiple units. Here is what comes back:

getData():

[
‘name’ => ‘test’,
‘username’ => ‘test’,
‘password’ => ‘aaaaaa’,
‘role’ => ‘M’,
‘units’ => [
‘_id’ => [
(int) 0 => ‘2’,
(int) 1 => ‘4’
]
],
‘email’ => ‘’
]

patchEntity():

object(App\Model\Entity\User) {

'name' => 'test',
'username' => 'test',
'password' => '$2y$10$hzrrgEtzn0ZYmBLSeH8fg.3FXXbvhyHpTAWOBJqBqOTi3pJcQ77vm',
'role' => 'M',
'units' => [
	(int) 0 => object(App\Model\Entity\Unit) {

		'[new]' => true,
		'[accessible]' => [
			'description' => true,
			'census' => true,
			'active' => true,
			'unit_type_id' => true,
			'unit_type' => true,
			'beds' => true,
			'users' => true
		],
		'[dirty]' => [],
		'[original]' => [],
		'[virtual]' => [],
		'[hasErrors]' => true,
		'[errors]' => [
			'description' => [
				'_required' => 'This field is required'
			]
		],
		'[invalid]' => [],
		'[repository]' => 'Units'
	
	}
],
'email' => '',
'[new]' => true,
'[accessible]' => [
	'name' => true,
	'username' => true,
	'password' => true,
	'role' => true,
	'email' => true,
	'passkey' => true,
	'timeout' => true,
	'units' => true
],
'[dirty]' => [
	'name' => true,
	'username' => true,
	'password' => true,
	'role' => true,
	'units' => true,
	'email' => true
],
'[original]' => [],
'[virtual]' => [],
'[hasErrors]' => true,
'[errors]' => [],
'[invalid]' => [],
'[repository]' => 'Users'

}

Because of the error shown, If I change the description field to optional in the UnitsTables file to see what would happen. I get this:

object(App\Model\Entity\User) {

'name' => 'test',
'username' => 'test',
'password' => '$2y$10$pTWzlSTnVU8w5z5bmRF2jewVdzD22H0aB.gQpiZpu9r9Xuu/k4zlm',
'role' => 'M',
'units' => [
	(int) 0 => object(App\Model\Entity\Unit) {

		'[new]' => true,
		'[accessible]' => [
			'description' => true,
			'census' => true,
			'active' => true,
			'unit_type_id' => true,
			'unit_type' => true,
			'beds' => true,
			'users' => true
		],
		'[dirty]' => [],
		'[original]' => [],
		'[virtual]' => [],
		'[hasErrors]' => false,
		'[errors]' => [],
		'[invalid]' => [],
		'[repository]' => 'Units'
	
	}
],
'email' => '',
'[new]' => true,
'[accessible]' => [
	'name' => true,
	'username' => true,
	'password' => true,
	'role' => true,
	'email' => true,
	'passkey' => true,
	'timeout' => true,
	'units' => true
],
'[dirty]' => [
	'name' => true,
	'username' => true,
	'password' => true,
	'role' => true,
	'units' => true,
	'email' => true
],
'[original]' => [],
'[virtual]' => [],
'[hasErrors]' => false,
'[errors]' => [],
'[invalid]' => [],
'[repository]' => 'Users'

}

But this throws an error in the save() process. Not sure what made it crash but units does not look like it’s correct after the patch.

Why did you change from _ids to _id?

That was a mistake on my part when I put the field selection code back in the template. Funny thing is, it’s now working. The join table records are being created. Been working backwards to figure out what the actual root cause was. Made so many changes over the course of troubleshooting, I can’t figure out what actually fixed it. But at least I have a working model. I did have to dump the composite key on the join table and go back to a table id field as the primary key. The problem was subsequent adds were replacing the user_id on an existing join record for a different user. Once I went back the the table id, the problem went away and new records were created.

The add() and delete() are working. Now I just need to work on the edit().

Hey Z, thanks for the patience while helping me work through this.

1 Like