[FIXED] Creating Association with condition using other association in CakePHP 3

Issue

I'm building a cake php 3 app. My app model includes 3 Tables (amongst others):

• Structures
• MeasuringPoints
• DeviceTypes

where each Strcuture can have multiple MeasuringPoints:

// StrcuturesTable.php
...
public function initialize(array $config)
{
    parent::initialize($config);

    ...

    $this->hasMany('MeasuringPoints', [
        'foreignKey' => 'structure_id'
    ]);
}

Further, each measuring point is of a certain device type:

// MeasuringPointsTable.php
...
public function initialize(array $config)
{
    parent::initialize($config);
    ...
    $this->belongsTo('DeviceTypes', [
        'foreignKey' => 'device_type_id',
        'joinType' => 'INNER'
    ]);
}

What i'm lookong for, is how to create a 'SpecialMeasuringPoints' association in the Structure table.

Somewhat like:

// MeasuringPointsTable.php
...
    $this->hasMany('SpecialMeasuringPoints',[
            'className' => 'MeasuringPoints',
            'foreignKey' => 'structure_id',
            'conditions' => ['MeasuringPoints.DeviceTypes.id'=>1]
    ]);

As you may see, I want only those measuring points, whose associated device type has the id 1. However, the previous association condition is not valid; and i have no clue how to correctly implement this.

Any help is appreciated.

Solution

Correct, that condition is invalid, for a number of reasons. First of all paths aren't supported at all, and even if they were, you already are in MeasuringPoints, respectively SpecialMeasuringPoints, so there would be no need to indicate that again.

While it would be possible to pass a condition like:

'DeviceTypes.id' => 1

That would require to alawys contain DeviceTypes when retrieving SpecialMeasuringPoints.

I would suggest to use a finder, that way you can easily include DeviceTypes and match against your required conditions. Something like:

$this->hasMany('SpecialMeasuringPoints',[
    'className' => 'MeasuringPoints',
    'foreignKey' => 'structure_id',
    'finder' => 'specialMeasuringPoints'
]);

In your MeasuringPoints class define the appropriate finder, for example using matching(), and you should be good:

public function findSpecialMeasuringPoints(\Cake\ORM\Query $query) {
    return $query
        ->matching('DeviceTypes', function (\Cake\ORM\Query $query) {
            return $query
                ->where([
                    'DeviceTypes.id' => 1
                ]);
        });
}

Similar could be done via the conditions option when passing a callback, which however is less DRY:

$this->hasMany('SpecialMeasuringPoints',[
    'className' => 'MeasuringPoints',
    'foreignKey' => 'structure_id',
    'conditions' => function (
            \Cake\Database\Expression\QueryExpression $exp,
            \Cake\ORM\Query $query
        ) {
            $query
                ->matching('DeviceTypes', function (\Cake\ORM\Query $query) {
                    return $query
                        ->where([
                            'DeviceTypes.id' => 1
                        ]);

            return $exp;
        }
]);

It should be noted that in both cases you need to be aware that such constructs are not compatible with cascading/dependent deletes, so do not try to unlink/delete via such associations!

See also

• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Custom Finder Methods
• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Filtering by Associated Data

Answered By - ndm