Monday, February 7, 2022

[FIXED] How to create a self BelongsToMany relation in CakePHP 3

      , , ,      No comments   

Issue

I have a database with a table "projekte" (german word for projects). Some of the projects have a relation to eachother. So i would like to have a BTM relation.

I created a joinTable "projektverbindungen" with the following fields:

projekt_id
nebenprojekt_id

I found a similar question here: BelongstoMany relationship between a table and itself and i tried the answer of ndm, but without success.

Here is my ProjekteTable.php

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

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

    $this->hasOne('Projekteigenschaften', [
      'foreignKey' => 'projekt_id',
      'dependent' => true,
    ]);
    $this->belongsToMany('Projekte', [
      'foreignKey' => 'projekt_id',
      'targetForeignKey' => 'nebenprojekt_id',
      'joinTable' => 'projektverbindungen',
    ]);
  }
}

Here ist my template (add.ctp)

<?php
echo $this->Form->create($projekt);
echo $this->Form->control('name', ['class' => 'form-control']);
echo $this->Form->control('projekteigenschaft.projektverantwortlich');
echo $this->Form->control('projekteigenschaft.beschreibung');
echo $this->Form->control('projekte._ids', ['options' => $projekte, 'multiple' => true]);
echo $this->Form->button(__('Submit'));
echo $this->Form->end();
?>

The first step, saving a project with a related project works as expected. The id of the created project was saved as projektverbindungen.projekt_id and the id of the related project as projektverbindungen.nebenprojekt_id.

When i query a projekt without the relation to other projects like so:

$projekt = $this->Projekte->get($id, [
  'contain' => ['Projekteigenschaften']
]);

the query looks like this:

SELECT Projekte.id AS `Projekte__id`, Projekte.name AS `Projekte__name`, Projekteigenschaften.id AS `Projekteigenschaften__id`, Projekteigenschaften.projektverantwortlich AS `Projekteigenschaften__projektverantwortlich`, Projekteigenschaften.beschreibung AS `Projekteigenschaften__beschreibung`, Projekteigenschaften.projekt_id AS `Projekteigenschaften__projekt_id` FROM projekte Projekte LEFT JOIN projekteigenschaften Projekteigenschaften ON Projekte.id = (Projekteigenschaften.projekt_id) WHERE (Projekte.id = :c0 AND (Projekte.deleted) IS NULL)

And the debug of the result looks like:

"id": "6862279f-8134-401f-86ff-9278a3bfa5c3",
"name": "My Project",
"projekteigenschaft": {
    "id": "89d9e241-e700-4c31-9266-ee5717f2a0aa",
    "projektverantwortlich": "Blisginnis, Ralf",
    "beschreibung": ""
}

Everything works fine.

But when i add the projects to contain like so:

$projekt = $this->Projekte->get($id, [
  'contain' => ['Projekteigenschaften', 'Projekte']
]);

The query looks the same like above, but the entity looks a bit different:

"Projekteigenschaften": {
    "id": "89d9e241-e700-4c31-9266-ee5717f2a0aa",
    "projektverantwortlich": "Blisginnis, Ralf",
    "beschreibung": ""
}

Projekteigenschaften seems no longer to be a hasOne relation and "Projekte" gets totally ignored.

Anyone has an idea what i did wrong? Or should i prefer an other way of doing this?

edit after ndm´s comment

I tried defining the relationship like so:

$this->belongsToMany('Projektverbindungen', [
  'class' => 'Projekte',
  'foreignKey' => 'projekt_id',
  'targetForeignKey' => 'nebenprojekt_id',
  'joinTable' => 'projektverbindungen',
]);

and changed the add.ctp template like so:

echo $this->Form->control('projektverbindungen._ids', ['options' => $projekte, 'multiple' => true]);

But then it doesn´t save the relation.

I also tried to rename the joinTable to projekte_projekte. It didn´t seem to make any difference.

Then I tried to use the through-option, but the results of that were even worse. So I continued trying to find a solution with the method described above.

2nd edit

projekverbindungen ist accessible in Projekt.php:

protected $_accessible = [
  'name' => true,
  'projekteigenschaft' => true,
  'projekte' => true,
  'projektverbindungen' => true,
];

Debug of requestData:

[
  'name' => 'My Project',
  'projekteigenschaft' => [
    'projektverantwortlich' => 'John Doe',
    'beschreibung' => '',
  'projektverbindungen' => [
    '_ids' => [
      (int) 0 => '809031f2-4ecd-4dfb-82d5-2c911286dd21'
    ]
  ]
]

Debug of entity after patching:

object(App\Model\Entity\Projekt) {

  'name' => 'My Project',
  'projekteigenschaft' => object(App\Model\Entity\Projekteigenschaft) {

    'projektverantwortlich' => 'John Doe',
    'beschreibung' => '',
    '[new]' => true,
    '[accessible]' => [
        'projektverantwortlich' => true,
        'beschreibung' => true,
        'projekt_id' => true,
        'projekt' => true
    ],
    '[dirty]' => [
        'projektverantwortlich' => true,
        'beschreibung' => true
    ],
    '[original]' => [],
    '[virtual]' => [],
    '[hasErrors]' => false,
    '[errors]' => [],
    '[invalid]' => [],
    '[repository]' => 'Projekteigenschaften'

  },
  'projektverbindungen' => [],
  '[new]' => true,
  '[accessible]' => [
    'name' => true,
    'projekteigenschaft' => true,
    'projekte' => true,
    'projektverbindungen' => true
  ],
  '[dirty]' => [
    'name' => true,
    'projekteigenschaft' => true,
    'projektverbindungen' => true
  ],
  '[original]' => [],
  '[virtual]' => [],
  '[hasErrors]' => false,
  '[errors]' => [],
  '[invalid]' => [],
  '[repository]' => 'Projekte'
}

3rd edit

In my bootstrap.php i have this:

Inflector::rules('plural', [
  '/^(projekt)$/i' => '\1e',
  '/^(projekteigenschaft|projektverbindung)$/i' => '\1en',
]);
Inflector::rules('singular', [
  '/^(projekt)e$/i' => '\1',
  '/^(projekteigenschaft|projektverbindung)en$/i' => '\1',
]);

After your recommendation I additionally added propertyName to the definition of the association:

$this->belongsToMany('Projektverbindungen', [
  'class' => 'Projekte',
  'propertyName' => 'Projektverbindungen',
  'foreignKey' => 'projekt_id',
  'targetForeignKey' => 'nebenprojekt_id',
  'joinTable' => 'projektverbindungen',
]);

After that, the patched entity looks like this:

object(App\Model\Entity\Projekt) {

  'name' => 'My Project',
  'projekteigenschaft' => object(App\Model\Entity\Projekteigenschaft) {

    'projektverantwortlich' => 'John Doe',
    'beschreibung' => '',
    '[new]' => true,
    '[accessible]' => [
        'projektverantwortlich' => true,
        'beschreibung' => true,
        'projekt_id' => true,
        'projekt' => true
    ],
    '[dirty]' => [
        'projektverantwortlich' => true,
        'beschreibung' => true
    ],
    '[original]' => [],
    '[virtual]' => [],
    '[hasErrors]' => false,
    '[errors]' => [],
    '[invalid]' => [],
    '[repository]' => 'Projekteigenschaften'

  },
  'projektverbindungen' => [
    '_ids' => [
        (int) 0 => '1e28a3d1-c914-44be-b821-0e87d69cd95f'
    ]
  ],
  '[new]' => true,
  '[accessible]' => [
    'name' => true,
    'projekteigenschaft' => true,
    'projekte' => true,
    'projektverbindungen' => true
  ],
  '[dirty]' => [
    'name' => true,
    'projekteigenschaft' => true,
    'projektverbindungen' => true
  ],
  '[original]' => [],
  '[virtual]' => [],
  '[hasErrors]' => false,
  '[errors]' => [],
  '[invalid]' => [],
  '[repository]' => 'Projekte'
}

But still no new entry in the table "projektverbindungen"


Solution

The last suggestion of ndm made the trick. Now it works like expected. Thank you very much!

Here is the correct setup:

ProjekteTable.php

  $this->belongsToMany('Nebenprojekte', [
    'className' => 'Projekte',
    'foreignKey' => 'projekt_id',
    'targetForeignKey' => 'nebenprojekt_id',
    'joinTable' => 'projektverbindungen',
  ]);

Here I used the property class instead of className, that has been the biggest issue. Thats really embarrassing, because in the Cookbook is the correct name of that property: https://book.cakephp.org/3/en/orm/associations.html#belongstomany-associations

Nevertheless, perhaps anyone else makes the same mistake and this thread can help.

The second thing is not to use the jointable`s name as the name of the association.

The rest is just straight forward...

Making the association accessible in the Entity Class (Projekt.php):

protected $_accessible = [
  'name' => true,
  'projekteigenschaft' => true,
  'nebenprojekte' => true,
];

ProjekteController.php ("add" and "edit"):

public function add()
{
    $projekt = $this->Projekte->newEntity();
    if ($this->request->is('post')) {
        $projekt = $this->Projekte->patchEntity($projekt, $this->request->getData());
        if ($this->Projekte->save($projekt)) {
            $this->Flash->success(__('flash message'));

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

    $projekte = $this->Projekte->find('list');
    $this->set(compact('projekt', 'projekte'));
}

public function edit($id = null)
{
    $projekt = $this->Projekte->get($id, [
      'contain' => ['Projekteigenschaften', 'Nebenprojekte']
    ]);

    if ($this->request->is(['patch', 'post', 'put'])) {
        $projekt = $this->Projekte->patchEntity($projekt, $this->request->getData());
        if ($this->Projekte->save($projekt)) {
            $this->Flash->success(__('flash message'));

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

    $projekte = $this->Projekte->find('list')->where(['id !=' => $id]);
    $this->set(compact('projekt', 'projekte'));
}

In the templates like add.ctp or edit.ctp:

echo $this->Form->control('nebenprojekte._ids', ['options' => $projekte, 'multiple' => true]);

If you use another language than english, don´t forget to set the correct inflection rules. bootstrap.php:

Inflector::rules('plural', [
  '/^(projekt|nebenprojekt)$/i' => '\1e',
  '/^(projekteigenschaft)$/i' => '\1en',
]);
Inflector::rules('singular', [
  '/^(projekt|nebenprojekt)e$/i' => '\1',
  '/^(projekteigenschaft)en$/i' => '\1',
]);


Answered By - exsand3r

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...