CakePHP 3-如何为同一个字段定义多个条件?

我正在CakePHP 3.5.13中开发一个应用程序。它绑定到一个“遗留”数据库,即一个尚未根据CakePHP命名约定编写的数据库。尽管如此,我还是烘焙了它并生成了表和实体文件。

我在使用ORM构造SQL查询时遇到问题。

在普通SQL中,查询如下所示:

SELECT s.id FROM substances s WHERE s.id 
IN
    (SELECT fs.substance_id FROM filters_substances fs WHERE fs.filter_id IN (147))
AND s.id IN 
    (SELECT fs.substance_id FROM filters_substances fs WHERE fs.filter_id IN (47,93))
AND s.id NOT IN
    (SELECT fs.substance_id FROM filters_substances fs WHERE fs.filter_id IN (148,354))

我已经使用ORM编写了以下内容:

$subquery_in = $FiltersSubstances
    ->find()
    ->select(['FiltersSubstances.substance_id'])
    ->where(['FiltersSubstances.filter_id IN' => [47,93]]);

$subquery_not_in = $FiltersSubstances
    ->find()
    ->select(['FiltersSubstances.substance_id'])
    ->where(['FiltersSubstances.filter_id IN' => [354]]);

$Substances = TableRegistry::get('Substances');

$query = $Substances
    ->find()
    ->where([
        'Substances.id IN' => $subquery_in,
        'Substances.id NOT IN ' => $subquery_not_in
    ]);

debug($query);
debug($query->all());

上面的查询工作正常,但不完整:它将提供所有结果,但不考虑包含以下内容的行(在普通SQL中) fs.filter_id IN (147) 。

我的问题是,我不知道如何生成多个 AND IN 子查询。

我尝试了以下操作:

// Same as PHP given previously, plus:

$subquery_in2 = $FiltersSubstances
    ->find()
    ->select(['FiltersSubstances.substance_id'])
    ->where(['FiltersSubstances.filter_id IN' => [147]]);

// Modified $query:

$query = $Substances
    ->find()
    ->where([
        'Substances.id IN' => [$subquery_in, $subquery_in2],
        'Substances.id NOT IN ' => $subquery_not_in]
    );

// Also tried:

$query = $Substances
    ->find()
    ->where([
        'Substances.id IN' => $subquery_in,
        'Substances.id IN' => $subquery_in2,
        'Substances.id NOT IN ' => $subquery_not_in
    ]);

上述尝试无效-SQL错误。

请告知是否/如何在ORM中构造此查询?

我曾考虑过采用“原始SQL”的手工编写方法。这似乎是一个巨大的耻辱,因为ORM正在做我需要的95%。

我读过 Cakephp 3 NOT IN query 这就是我如何设法完成工作所需的第一部分。然而,该示例中的查询可以说不那么复杂,并且不能满足我在这里的需要。

重要的 :原始SQL生成正确的结果。我知道有些人可能会说,为什么不把所有 IN ID进入一个查询。然而,这将产生非常不同的——而且是不正确的——结果。这个 IN... AND IN 在这里非常重要。我知道查询很好,我感兴趣的是如何在Cake的ORM中编写查询。