[FIXED] Cakephp 3 How to access deep contain associations in where clause?

Issue

I have an issue with Cakephp 3.3 in which I do not know how to access linked data by using contain in a where clause.

Here is the request :

$cables = TableRegistry::get('cable_schedule');

    $cablemark = 'test1';
    $equipement = 'test2';
    $compartment = 'test3';
    $system = 'test4';

    $query = $cables->find('all')->contain(['CableType', 'Contract', 'EquipementSource' => ['Compartment', 'System'], 'EquipementDest' => ['Compartment', 'System']])
        ->where(['EquipementSource.Description like' => '%'.$equipement.'%'])
        ->orWhere(['EquipementDest.Description like' => '%'.$equipement.'%'])
        ->andWhere(['Cable_Mark like' => '%'.$cablemark.'%'])
        ->andWhere(['EquipementSource.Compartment.Description like' => '%'.$compartment.'%'])
        ->orWhere(['EquipementDest.Compartment.Description like' => '%'.$compartment.'%'])
        ->andWhere(['EquipementSource.System.Description like' => '%'.$system.'%'])
        ->orWhere(['EquipementDest.System.Description like' => '%'.$system.'%']);

    $this->set('cables', $query);

The error is :

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'EquipementDest.System.Description' in 'where clause'

Solution

You need to use the matching() method (see the CakePHP manual).

For example, if you have table articles and it has many tags, you could filter results like this:

$query = $articles->find();
$query->matching('Tags', function ($q) {
    return $q->where(['Tags.name' => 'CakePHP']);
});

Answered By - makallio85