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

Question

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'

it depends on the relationships you have between your models. If all the relationships are belongsTo type then your query is correct. But I guess they are hasMany or belongsToMany. In that case you have to use matching() clause — arilia

makallio85 makallio85 · Accepted Answer · 2016-11-11T04:01:10

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

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

1 Answers