CakePHP 3.3.15 multiple matching() chained with OR

Question

I'm trying to implement a global search mechenism in my CakePHP 3.3.15 app - this involves searching the Parent table for a value plus searching all the associated tables for the same value. I'm using matching(), but I can't find a way to combine multiple matching() into one query object using OR.

It looks like matching() will narrow down the result set and calling matching() consecutively will use the object modified by the previous matching() call.

This is what I have:

$query = $this->$modelName->find();         
$query->matching('Colonies', function ($q) {
                return $q->where(['OR' => [
                    'Colonies.colony_id' => 10003,
                    'Colonies.project_id' => 6
                ]]);
            });
debug($query->count()); //returns 4 entries

$query->matching('Projects', function ($q) {
                return $q->where(['OR' => [
                    'Projects.id' => 1,
                    'Projects.project_status_id' => 3
                ]]);
            });
debug($query->count()); //returns 1 entry. However, when the previous matching() is removed, it will return 2 entries

What else I have tried so far:

1. using innerJoinWith() instead of matching() - same result, just the result set is lacking associated fields (exactly as said in the CookBook)
2. using $this->$modelName->find()->contain('ChildTable', function ($q) { return $q->where(['ChildTable.FieldName' => 'some value']}); - this does not seem to work at all (doesn't filter the result set)
3. Combining the result sets manually to force OR behavior - I'm using pagination later in the code, so I can't work on the result sets directly - I need a single Query object to pass to the paginator.

Any help greatly appreciated!

Answer 1

Matching will apply conditions on JOIN level, so each set of matching() conditions will apply independently, and since matching() creates INNER joins, both joins will exclude records.

Check the generated SQL, your multiple matching() conditions will create SQL similar to:

INNER JOIN colonies ON colonies.colony_id = 10003 OR colonies.project_id = 6
INNER JOIN projects ON projects.id = 1 OR projects.project_status_id = 3

What you are looking for requires to use A) LEFT joins, so that the joins themselves do not excude records, and B) to apply the conditions on the main query, in order to be able to filter the records based on the joined tables:

$query = $this->$modelName
    ->find()
    ->leftJoinWith('Colonies')
    ->leftJoinWith('Projects')
    ->where([
        'OR' => [
            'Colonies.colony_id' => 10003,
            'Colonies.project_id' => 6,
            'Projects.id' => 1,
            'Projects.project_status_id' => 3
        ]
    ])
    ->group($modelName . '.id');

Due to the nature of joins you may also need to group on the main models primary key in order to avoid dupplicate results.

See also

• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Using leftJoinWith