How to setup multiple HABTM relationships between two tables in CakePHP

Question

I'm trying to make two HABTM relationships between these two tables in CAKEPHP 2.6, but it gives me a database error when I try to create a record. I'm sure there must be some model settings or something, but I could not fix it. :(

Here's an img of DB structure:

Model

class Solicitude extends AppModel {
public $hasAndBelongsToMany = array(
    'Citante' => array(
        'className' => 'Cliente',
        'joinTable' => 'citantes',
        'foreignKey' => 'solicitude_id',
        'associationForeignKey' => 'cliente_id',
        'unique' => 'keepExisting',
        'conditions' => '',
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'finderQuery' => '',
    ),
    'Citado' => array(
        'className' => 'Cliente',
        'joinTable' => 'citados',
        'foreignKey' => 'solicitude_id',
        'associationForeignKey' => 'cliente_id',
        'unique' => 'keepExisting',
        'conditions' => '',
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'finderQuery' => '',
    )
);

}

Controller ADD method

public function add() {
    if ($this->request->is('post')) {
        $this->Solicitude->create();
        if ($this->Solicitude->save($this->request->data)) {
            $this->Session->setFlash(__('The solicitude has been saved.'));
            return $this->redirect(array('action' => 'index'));
        } else {
            $this->Session->setFlash(__('The solicitude could not be saved. Please, try again.'));
        }
    }
    $citantes = $this->Solicitude->Citante->find('list');
    $citados = $this->Solicitude->Citado->find('list');
    $this->set(compact('citantes', 'citados'));
}

Add view

echo $this->Form->create('Solicitude'); 

echo __('Add Solicitude');

    echo $this->Form->input('radicado');
    echo $this->Form->input('fecha');
    echo $this->Form->input('ccsede_id');
    echo $this->Form->input('ccusuario_id');
    echo $this->Form->input('consulta_id');
    echo $this->Form->input('peticiones');
    echo $this->Form->input('area_id');
    echo $this->Form->input('tipo_clase_id');
    echo $this->Form->input('Citante');
    echo $this->Form->input('Citado');

echo $this->Form->end(__('Submit'));

Error obtained adding

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Citante.cliente_id' in 'field list'

SQL Query: SELECT Citante.cliente_id FROM conciliacion.clientes AS Citante WHERE Citante.solicitude_id = '1'

Answer 1

With CakePHP 2 you can define these relations like this in your Solicitude model:

var $hasAndBelongsToMany = array(

        'Citantes' => array(
            'className' => 'Client',
            'joinTable' => 'citantes',
            'foreignKey' => 'solicitude_id',
            'associationForeignKey' => 'cliente_id'
        ),
        'Citados' => array(
            'className' => 'Client',
            'joinTable' => 'citados',
            'foreignKey' => 'solicitude_id',
            'associationForeignKey' => 'cliente_id'
        )
    );

The only thing you have to two is to set two different relation names, here Citantes and Citados. They are then the names that are used to fetch records.

And with CakePHP 3, this would be done like this in your SolicitudesTable table:

$this->belongsToMany('Citantes', [
            'className' => 'Client',
            'joinTable' => 'citantes',
            'foreignKey' => 'solicitude_id',
            'targetForeignKey' => 'cliente_id'
        ]);

$this->belongsToMany('Citados', [
            'className' => 'Client',
            'joinTable' => 'citados',
            'foreignKey' => 'solicitude_id',
            'targetForeignKey' => 'cliente_id'
        ]);