CakePHP ORM : Query with a join on the same table with a non-standard association

Question

I have a very particular use case, and i can't find a clean solution with the ORM. I've searched a lot, and maybe my database model is not correct, I'm not sure.

I use CakePHP 3.8.11.

So, I have a table "MaintenanceTypes" with 3 important fields : id, name, and periodicity. Periodicity (in days) means "this maintenance is to be done every (for instance) 30 days".

Periodicity are like 7 (week), 30 (month), 90 (trimester) and so on.

I also have a table "Operations", they are little unit tests that belongs to a "MaintenanceType" (fields are id, name, maintenance_type_id).

What is special in this case, is that, as a business rule, Operations belonging to a MaintenanceType with a periodicity of 7 days is "included" in every MaintenanceType with a greater periodicity; that means that every trimester, you should do every Operations associated directly to the trimester, but also every Operations associated with the month, and the week, etc.

In raw SQL it's trivial :slight_smile:

mt_ref is the reference MaintenanceType, mt_inc are the included MaintenanceTypes (with a lesser periodicity) and finally, every Operations belonging to any of the MaintenanceTypes found.

    SELECT mt_ref.id, mt_ref.name, mt_ref.periodicity, 
        mt_inc.name, mt_inc.periodicity, o.name 
    FROM maintenance_types mt_ref 
        LEFT JOIN maintenance_types mt_inc 
            ON (mt_inc.periodicity <= mt_ref.periodicity) 
        LEFT JOIN operations o ON (o.maintenance_type_id = mt_inc.id) 
    WHERE mt_ref.id = 3 

I've tried to declare the association between MaintenanceTypes, but I can't find a way to declare that the association is done on the periodicity field, and, extra points, not on a equality but on a "less or equal".

To add extra difficulties, I use this query for a (very good) JQuery Datatables CakePHP plugin (https://github.com/allanmcarvalho/cakephp-datatables), so I can't simply pass the raw SQL, and I must use the ORM...

I hope this is clear, and that someone could help me on this one !

Thanks a lot !

Answer 1

If you need query builder instances, then pretty much have two options here that are more or less straightforward, that is either use associations with custom conditions, or manual joins.

Custom association conditions

With associations you'd probably do something like a self-association with MaintenanceTypes with a disabled foreign key and custom conditions, like so in your MaintenanceTypesTable class:

$this
    ->hasMany('MaintenanceTypesInc')
    ->setClassName('MaintenanceTypes')
    ->setForeignKey(false)
    ->setConditions(function (
        \Cake\Database\Expression\QueryExpression $exp,
        \Cake\ORM\Query $query
    ) {
        return $exp->lte(
            $query->identifier('MaintenanceTypesInc.periodicity'),
            $query->identifier('MaintenanceTypes.periodicity')
        );
    });

Disabling the foreign key will prevent the ORM from creating the default A.fk = B.fk condition when joining in the association. It should be noted that you cannot contain a hasMany association with a disabled foreign key, you can only join it in! You could use a hasOne or even belongsTo association instead, but it would kinda be a lie, as you don't have a 1:1 relationship here (at least as far as I understand it).

Also note that you don't neccesarily have to use a callback with all expressions for the conditions, you could pass the conditions as a key => value array with a manually instantiated identifier expression, or even as simple string (the latter however will not be recognized when using automatic identifier quoting):

->setConditions([
    'MaintenanceTypesInc.periodicity <=' =>
        new \Cake\Database\Expression\IdentifierExpression('MaintenanceTypes.periodicity');
]);

->setConditions('MaintenanceTypesInc.periodicity <= MaintenanceTypes.periodicity');

Assuming you also have an association for Operations in your MaintenanceTypesTable class, you should be able to join in both, the new association and the Operations association via the query builders *JoinWith() methods, like this:

$query = $maintenanceTypesTable
    ->find()
    ->select([
        'MaintenanceTypes.id', 'MaintenanceTypes.name', 'MaintenanceTypes.periodicity',
        'MaintenanceTypesInc.name', 'MaintenanceTypesInc.periodicity',
        'Operations.name',
    ])
    ->leftJoinWith('MaintenanceTypesInc.Operations');

In the results, the association data will be put under the _matchingData key, ie you can obtain it like $entity->_matchingData->MaintenanceTypesInc and $entity->_matchingData->Operations. If you don't want that, then you need to use aliases for the fields of the associations, like:

->select([
    'MaintenanceTypes.id', 'MaintenanceTypes.name', 'MaintenanceTypes.periodicity',
    'mt_inc_name' => 'MaintenanceTypesInc.name', 'mt_inc_periodicity' => 'MaintenanceTypesInc.periodicity',
    'op_name' => 'Operations.name',
])

If you don't want to select all the fields everytime, use a custom finder as in the manual joins example below.

Manual joins

Using manual joins gives you complete freedom, with the query builders *Join() methods you can create whatever joins you like, and you don't have to use possible workarounds with associations.

You can add them in a custom finder for proper reusability, it could look something like this in your MaintenanceTypesTable class:

public function findWithIncludedMaintenanceTypes(\Cake\ORM\Query $query, array $options)
{
    return $query
        ->select(/* ... */)
        ->leftJoin(
            ['MaintenanceTypesInc' => 'maintenance_types'],
            function (
                \Cake\Database\Expression\QueryExpression $exp,
                \Cake\ORM\Query $query
            ) {
                return $exp->lte(
                    $query->identifier('MaintenanceTypesInc.periodicity'),
                    $query->identifier('MaintenanceTypes.periodicity')
                );
            }
        )
        ->leftJoin(
            ['Operations' => 'operations'],
            function (
                \Cake\Database\Expression\QueryExpression $exp,
                \Cake\ORM\Query $query
            ) {
                return $exp->equalFields(
                    'Operations.maintenance_type_id ',
                    'MaintenanceTypesInc.id'
                );
            }
        );
}

Then you simply use the finder wherever you need it, like this:

$query = $maintenanceTypesTable
    ->find('withIncludedMaintenanceTypes');

Note that just like in the associations example, you can use string or array conditions for the custom joins too.

See also

• Cookbook > Database Access & ORM > Associations - Linking Tables Together
• Cookbook > Database Access & ORM > Query Builder > Loading Associations
• Cookbook > Database Access & ORM > Query Builder > Loading Associations > Using leftJoinWith
• Cookbook > Database Access & ORM > Query Builder > Loading Associations > Adding Joins
• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Using Finders to Load Data
• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Custom Finder Methods