CakePHP 3.5.13
I have several Models and each has a Table class with relationships defined.
// src/Model/Table/DisplaySubstancesTable.php
public function initialize(array $config)
{
$this->belongsTo('Displays', [
'foreignKey' => 'display_id',
'joinType' => 'INNER'
]);
}
// src/Model/Table/Displays.php
public function initialize(array $config)
{
$this->belongsTo('Groups', [
'foreignKey' => 'group_id',
'joinType' => 'INNER'
]);
$this->hasMany('DisplaySubstances', [
'foreignKey' => 'display_id'
]);
}
// src/Table/Model/GroupsTable.php
public function initialize(array $config)
{
$this->hasMany('Displays', [
'foreignKey' => 'group_id'
]);
$this->belongsTo('Regulations', [
'foreignKey' => 'regulation_id',
'joinType' => 'INNER'
]);
}
// src/Table/Model/Regulations.php
public function initialize(array $config)
{
$this->hasMany('Groups', [
'foreignKey' => 'regulation_id'
]);
}
As you can see, these Models are related. The database table structures for each table have the appropriate keys. I've shown the table fields below (and removed any fields un-related to the question).
MySQL> DESCRIBE display_substances;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| display_id | smallint(5) unsigned | NO | MUL | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
MySQL> DESCRIBE displays;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| group_id | int(4) unsigned | NO | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+
MySQL> DESCRIBE groups;
+---------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------------+------+-----+---------+----------------+
| id | int(4) unsigned | NO | PRI | NULL | auto_increment |
| regulation_id | int(4) unsigned | NO | MUL | NULL | |
+---------------+-----------------+------+-----+---------+----------------+
MySQL> DESCRIBE regulations;
+-------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
+-------------------+---------------------+------+-----+---------+----------------+
In a Controller method I have the following code, which works:
$DisplaySubstances = TableRegistry::get('DisplaySubstances');
$data = $DisplaySubstances->find()
->contain(['Displays'])
->where(...)
->select(['Displays.id', 'Displays.label', 'Displays.anchor', 'DisplaySubstances.value']);
What I want to do is include only some Regulations.id
which are defined in an array $include
.
In order to do this I attempted to use contain()
so that it included Regulations
:
->contain(['Displays', 'Regulations'])
My plan was then to use:
->where(['Regulations.id IN' => $include]);
This fails on both accounts. When I added Regulations
to the array in contain()
it gives this error:
DisplaySubstances
is not associated withGroups
I'm unsure why because when I go through my Model associations that relationship has been defined.
I'm also getting an SQL error with ->where(['Regulations.id IN' => $include]);
but I believe this is because the relationship to make that work isn't occurring.
Please can someone advise how to use containable behaviour in CakePHP to make this work?