CakePHP: How to setup BelongsToMany with the same table?

Question

I have a table called processo_administrativo that has and belongs to many processo_administrativo through processo_administrativo_processo_administrativo

Both tables have id "identity(1,1)"

The join table, processo_administrativo_processo_administrativo have these columns:

both processo_administrativo_id and processo_administrativo_associado_id references to processo_administrativo.id

So, I've tried this on ProcessoAdministrativoTable :

$this->belongsToMany('ProcessoAdministrativo', [
            'targetForeignKey'=> 'processo_administrativo_associado_id',
            'foreignKey'=> 'processo_administrativo_id'
        ])
        ->setThrough('ProcessoAdministrativoProcessoAdministrativo');

And tried this find with contain:

    $result = $paTable->find()->contain('ProcessoAdministrativo')
        ->where(['id IN'=>[2947,3694]])
        ->toArray();
    
    debug($result);

But Cake did this (in the second query):

....
FROM 
  processo_administrativo ProcessoAdministrativo 
  INNER JOIN processo_administrativo_processo_administrativo ProcessoAdministrativoProcessoAdministrativo ON ProcessoAdministrativo.id = (
    ProcessoAdministrativoProcessoAdministrativo.processo_administrativo_id
  ) 
WHERE 
  ProcessoAdministrativoProcessoAdministrativo.processo_administrativo_id in (2947, 3694)

As you can see, for some reason CakePHP simply ignored the targetForeignKey (processo_administrativo_associado_id). I don't know why.

And the result are two ProcessoAdministrativo entities with no attributes and values from the database.

So, what could be the correct way to setup this association?

score 0 · Answer 1 · answered Jun 27 '20 at 20:09

0

What you are doing there should normally trigger an exception, as you cannot have two table aliases with the same name, ie there can be only one ProcessoAdministrativo, and that's already the default alias for your parent table.

If you want to create a self-referencing association, then you need to create a unique alias for that, and use the className option to reference the original table alias.

$this
    ->belongsToMany('ProcessoAdministrativoAssociado')
    ->setClassName('ProcessoAdministrativo')
    ->setTargetForeignKey('processo_administrativo_associado_id')
    ->setForeignKey('processo_administrativo_id')
    ->setThrough('ProcessoAdministrativoProcessoAdministrativoAssociado');

In order to avoid confusion and further problems with names that CakePHP expects, I would suggest to also adjust the conventions for table names, class names, and column names accordingly where required, ie at least also adjust the through class name and its table name (processo_administrativo_processo_administrativo_associado) accordingly.

Furthermore I would suggest that you use US English for your names, as that is what CakePHPs inflection that is used for inferring table/class/key/variable/etc names is based on - that will make your life way easier!

See also

answered Jun 27 '20 at 20:09

ndm

59,784
9
71
110

Hi, i got this error: Erro: Cannot describe processo_administrativo_processo_administrativo_associado. It has 0 columns. – celsowm Jun 27 '20 at 20:57
@celsowm That error usually occurs when the table doesn't exist, or isn't accessible (permission problems for example) – ndm Jun 27 '20 at 21:13
but the name is wrong, as you can see in my post, the join table is called "processo_administrativo_processo_administrativo" and not "processo_administrativo_processo_administrativo_associado" – celsowm Jun 27 '20 at 21:20
@celsowm Yes and no, `processo_administrativo_processo_administrativo_associado` is the correct name when you follow the naming conventions that I suggested, it is derived from the `through` alias/class name. I ment to suggest that you change your table and class name accordingly. Imagine you'd prepare things so that you could add multiple self-associations, for which you'd inevitably need unique and meaningful join table names. – ndm Jun 27 '20 at 21:56
Finally I did it: $this->belongsToMany('ProcessoAssociado', [ 'className'=>'ProcessoAdministrativo' ]) ->setTargetForeignKey('processo_administrativo_associado_id') ->setForeignKey('processo_administrativo_id') ->setThrough('processo_administrativo_processo_administrativo'); – celsowm Jun 28 '20 at 00:24
if you can please, update your answer with this and I will give the "v" – celsowm Jun 28 '20 at 00:34
@celsowm That's pretty much identical to what is my answer, you're just using a different alias, and your value for `setThrough()` is technically invalid according to the conventions, as that method/option is supposed to take a class name, or a class instance. – ndm Jun 28 '20 at 00:47

score 0 · Answer 2 · answered Jun 29 '20 at 12:46

The solution was:

     $this->belongsToMany('ProcessoAssociado', 
    [ 'className'=>'ProcessoAdministrativo' ])
 ->setTargetForeignKey('processo_administrativo_associado_id') 
->setForeignKey('processo_administrativo_id') 
->setThrough('processo_administrativo_processo_administrativo');

CakePHP: How to setup BelongsToMany with the same table?

2 Answers2