1

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 with Groups

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?

Andy
  • 5,142
  • 11
  • 58
  • 131
  • Double check that the code you're showing is correct (as in "matches what you're using"), the error is about `Groups`, but your are concentrating on `Regulations`, for which there isn't even an association shown anywhere. – ndm Apr 23 '20 at 15:28
  • @ndm I'd missed an association in `GroupsTable.php` in my post which I've since added (`belongsTo('Regulations', ... `). That was already in there though so doesn't answer the question. – Andy Apr 24 '20 at 08:33
  • I still don't see you using `Groups` anywhere? However if that's all associations now, then there is no `Groups` association on `DisplaySubstances`, just like the error message states. `Groups` is on `Displays`, and `Regulations` is on `Groups`, ie it's `DisplaySubstances > Displays > Groups > Regulations`, so in any case you'd have to properly nest/target the associations when containing them, see **https://book.cakephp.org/3/en/orm/query-builder.html#loading-associations**. – ndm Apr 24 '20 at 08:44
  • I should say I had tried both `->contain(['Displays', 'Regulations'])` and `->contain(['Displays', 'Groups'])`. The relationships you've described above are correct. But that's the point - I am using `$DisplaySubstances->find()` and want to query based on `Regulations.id`. Although there is no *direct* relationship between just `DisplaySubstances` and `Regulations` I assumed it's possible for the ORM to work out the relationships based on what's in the Table classes? As far as I can see, everything is there for it to be able to do that? – Andy Apr 24 '20 at 09:07
  • Got it now, `->contain(['Displays', 'Displays.Groups.Regulations'])` – Andy Apr 24 '20 at 09:17

0 Answers0