CakePHP3.4: strange error on a query about a particular HABTM association [SOLVED]

Need Help
1

I try to input a belongsToMany association in a Form but a quite particular association description.
Because my entity Site can use Languages for 2 different usages, I use 2 join tables.
So the schema is the following

Relationship 	                    Join Table Fields
Sites belongsToMany Vislanguages    sites_vislanguages.id, sites_vislanguages.language_id, sites_vislanguages.site_id
Relationship 	                    Join Table Fields
Sites belongsToMany Reclanguages    sites_reclanguages.id, sites_reclanguages.language_id, sites_reclanguages.site_id

So the Table classes are:

class VislanguagesTable extends Table {

	public function initialize(array $config) {
	    parent::initialize($config);
	    
	    $this->table('languages');
		$this->displayField('name_fr');
		$this->primaryKey('id');

		$this->belongsToMany('Sites', [
 		    'foreignKey' => 'language_id',
			'targetForeignKey' => 'site_id',
			'joinTable' => 'sites_vislanguages',
		]);

	}
}

class SitesTable extends Table {

public function initialize(array $config) {
    parent::initialize($config);
    
    $this->belongsToMany('Reclanguages', [
        'joinTable' => 'sites_reclanguages',
        'className' => 'Languages',
        'propertyName' => 'reclanguages'
    ]);
    
    $this->belongsToMany('Vislanguages', [
    	'joinTable' => 'sites_vislanguages',
        'className' => 'Languages',
        'propertyName' => 'vislanguages'
    ]);

}

class SitesVislanguagesTable extends Table {

public function initialize(array $config) {
    parent::initialize($config);
    
    $this->table('sites_vislanguages');
	$this->displayField('id');
	$this->primaryKey('id');

	$this->belongsTo('Sites', [
		'foreignKey' => 'site_id',
	]);
	$this->belongsTo('Languages', [
		'foreignKey' => 'language_id',
	]);
}

I of course have the problem for add and edit forms, but I here take the example of edit.
If I find() a ready made site, the data structure is:

object(App\Model\Entity\Site) {

	'id' => (int) 23098,
	'Vislanguages' => [
		(int) 0 => object(App\Model\Entity\Language) {

			'id' => (int) 1,
			'_joinData' => object(App\Model\Entity\SitesVislanguage) {

				'id' => (int) 4409,
				'site_id' => (int) 23098,
				'language_id' => (int) 1,
				...,
				'[repository]' => 'SitesVislanguages'
			
			},
			...,
			'[repository]' => 'Vislanguages'
		
		},
		(int) 1 => object(App\Model\Entity\Language) {

			'id' => (int) 9,
			'_joinData' => object(App\Model\Entity\SitesVislanguage) {

				'id' => (int) 4410,
				'site_id' => (int) 23098,
				'language_id' => (int) 9,
				...,
				'[repository]' => 'SitesVislanguages'
			
			},
			...,
			'[repository]' => 'Vislanguages'
		
		}
	],
	...,
	'[repository]' => 'Sites'

}

And my corresponding ctp file is:

<?= $this->Form->control('vislanguages._ids', ['options' => $languages, 'label' => __('Spoken languages:'), 'multiple' => true]); ?>

The languages are correctly preselected in the input.
If I submit it without any change, the patched entity is:

object(App\Model\Entity\Site) {

'id' => (int) 23098,
...,
'vislanguages' => [
	(int) 0 => object(App\Model\Entity\Language) {

		'id' => (int) 1,
		...,
		'[repository]' => 'Vislanguages'

	},
	(int) 1 => object(App\Model\Entity\Language) {

		'id' => (int) 9,
		...,
		'[repository]' => 'Vislanguages'

	}
],
'[repository]' => 'Sites'

}

Which seems to be correct but when I save it, I get the following error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'vislanguage_id' in 'where clause'

The query is:

(SELECT SitesVislanguages.id AS `SitesVislanguages__id`, SitesVislanguages.site_id AS `SitesVislanguages__site_id`, SitesVislanguages.language_id AS `SitesVislanguages__language_id` FROM sites_vislanguages SitesVislanguages WHERE (site_id = :c0 AND vislanguage_id = :c1))
UNION (SELECT SitesVislanguages.id AS `SitesVislanguages__id`, SitesVislanguages.site_id AS `SitesVislanguages__site_id`, SitesVislanguages.language_id AS `SitesVislanguages__language_id` FROM sites_vislanguages SitesVislanguages WHERE (site_id = :c2 AND vislanguage_id = :c3))

Why do we see vislanguage_id in the WHERE clause whereas it correctly considers language_id instead in the SELECT clause ?

In the same time, I don’t really understand the UNION here.

2

To get it working, I had to specify the targetForeignKey:

class SitesTable extends Table {

public function initialize(array $config) {
    parent::initialize($config);
    
    $this->belongsToMany('Reclanguages', [
        'targetForeignKey' => 'language_id,
        'joinTable' => 'sites_reclanguages',
        'className' => 'Languages',
        'propertyName' => 'reclanguages'
    ]);
    
    $this->belongsToMany('Vislanguages', [
        'targetForeignKey' => 'language_id,
    	'joinTable' => 'sites_vislanguages',
        'className' => 'Languages',
        'propertyName' => 'vislanguages'
    ]);

}
1 Like