CakePHP 3.3.15与OR链接的多个match() [英] CakePHP 3.3.15 multiple matching() chained with OR

问题描述

我正在尝试在我的 CakePHP 3.3.15 应用中实现全局搜索机制-这涉及到在Parent表中搜索一个值，然后在所有关联表中搜索相同的值.我正在使用matching()，但是我找不到使用OR 将多个matching()组合到一个查询对象中的方法.

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.

看起来matching()会缩小结果集的范围，并且连续调用matching()会使用上次matching()调用修改的对象.

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

这就是我所拥有的:

$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. 使用innerJoinWith()而不是match()-结果相同，只是结果集缺少关联的字段(完全与CookBook中所说的一样)
2. 使用$this->$modelName->find()->contain('ChildTable', function ($q) { return $q->where(['ChildTable.FieldName' => 'some value']});-这似乎根本不起作用(不过滤结果集)
3. 手动组合结果集以强制执行OR行为-我稍后在代码中使用分页，因此无法直接处理结果集-我需要一个查询对象才能传递给分页器.

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!

推荐答案

匹配将在JOIN级别应用条件，因此每组matching()条件将独立应用，并且由于matching()创建了INNER联接，两次连接都将排除记录.

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.

检查生成的SQL，您的多个matching()条件将创建类似于以下内容的SQL:

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

您要查找的内容需要使用A)LEFT联接，以便联接本身不会出现记录，以及B)在主查询上应用条件，以便能够基于以下条件过滤记录在联接表上:

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.

另请参见

• 食谱>数据库访问和ORM>检索数据和结果集>使用leftJoinWith

这篇关于CakePHP 3.3.15与OR链接的多个match()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！