[FIXED] Join table without id as column name - Cakephp 3

Issue

I have a big problem, I don't know how to join a table without passing '_id' as the referenced column name and also including multiple tables who do not have this constraint. For example, I want to join A.code = B.code_defaut

I tried both methods :

       $cycle = $this->Cycles->find('all')
                             ->where(['Cycles.id'=>$id])
                             ->contain(['Quais',
                             'Defauts',
                             'Traces'])
                             ->join([
                                 'ReferentielTraces'=>[
                                     'table'=>'referentiel_traces',
                                     'type'=>'LEFT',
                                     'conditions'=>'ReferentielTraces.code = Defauts.code_defaut'
                                 ]
                             ]);

       $cycle = $this->Cycles->find('all')
                             ->where(['Cycles.id'=>$id])
                             ->contain(['Quais',
                             'Defauts',
                             'Traces',
                             'Defauts.ReferentielTraces']);

But in the first case I have this problem (yes sorry it's french ^^ ):

Error: SQLSTATE[42P01]: Undefined table: 7 ERREUR: entrée manquante de la clause FROM pour la table « defauts » LINE 1: ...ces ReferentielTraces ON ReferentielTraces.code = Defauts.co... ^

which means " missing entry of the FROM clause for the table "Defauts".

And in the second case I have this:

Error: SQLSTATE[42883]: Undefined function: 7 ERREUR: l'opérateur n'existe pas : integer = character varying LINE 1: ..._traces ReferentielTraces ON ReferentielTraces.id = (Defauts... ^

which means "we can't compare integer and character varying " as they are trying to compare ReferentielTraces.id and Defauts.code_defaut

I also tried to make a group of join like join([firstTable, secondTable ...]). But I have no datas for Defauts, Traces, Quais and ReferentielTraces.

Here is the relationship between tables:

Cycles:

        //Quais.id = Cycles.quai_id
        $this->belongsTo('Quais', [
            'foreignKey' => 'quai_id'
        ]);

        //Defauts.cycle_id = Cycles.id
        $this->hasMany('Defauts', [
            'foreignKey' => 'cycle_id'
        ]);

        //Traces.cycle_id = Cycles.id
        $this->hasMany('Traces', [
            'propertyName'=>'Traces',
            'foreignKey' => 'cycle_id',
        ]);

Defauts:

//Defauts.code_defaut = ReferentielTraces.code
$this->belongsTo('ReferentielTraces',[
            'className'=>'ReferentielTraces',
            'foreignKey' => 'code_defaut'   
        ]);

Could you help me please?

Solution

You can use bindingKey , which will be something similar to the following :

//Defauts.code_defaut = ReferentielTraces.code
$this->belongsTo('ReferentielTraces',[
            'className'=>'ReferentielTraces',
            'foreignKey' => 'code_defaut',
            'bindingKey' => 'code'   
        ]);

Answered By - Shifat