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'

Welcome to Stackoverflow! To help with getting answers to your question faster, it would be great if you could share the code that you're trying to use at the moment. — scrowler, Jun 11 '15 at 02:47
Please put the code in your question and format it. It is not readable in a comment. — nIcO, Jun 11 '15 at 13:56
What line gives the error ? the `find('list')` ? These queries are done by the `Client` model, as `Citante` and `Citado` are aliases for `Client` and the find() is called on them. The problem is probably to look in the `Client` class. Most of the time, relations must be declared in both ways. — nIcO, Jun 12 '15 at 10:48

nIcO · Answer 1 · 2015-06-11T07:46:26.000

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'
        ]);

Hi...and thank u .. I have this in my model public $hasAndBelongsToMany = array( 'Citante' => array( 'className' => 'Cliente', 'joinTable' => 'citantes', 'foreignKey' => 'solicitude_id', 'associationForeignKey' => 'cliente_id', 'unique' => 'keepExisting', ), 'Citado' => array( 'className' => 'Cliente', 'joinTable' => 'citados', 'foreignKey' => 'solicitude_id', 'associationForeignKey' => 'cliente_id', 'unique' => 'keepExisting', ) ); — Erick Ajuria, Jun 11 '15 at 13:33
But when i m adding i m getting that error.. 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' — Erick Ajuria, Jun 11 '15 at 13:37

How to setup multiple HABTM relationships between two tables in CakePHP

1 Answers1