页面内容

查询构建器

查询构建器¶

class Cake\ORM\Query\SelectQuery\SelectQuery¶

ORM 的查询构建器提供了一个易于使用的流畅接口来创建和运行查询。通过将查询组合在一起，您可以轻松地使用联合和子查询创建高级查询。

在幕后，查询构建器使用 PDO 预处理语句，可以防止 SQL 注入攻击。

SelectQuery 对象¶

创建 SelectQuery 对象的最简单方法是使用 find() 从 Table 对象。此方法将返回一个未完成的查询，准备进行修改。如果需要，您还可以使用表的连接对象访问不包含 ORM 功能的较低级查询构建器。有关更多信息，请参阅执行查询部分

use Cake\ORM\Locator\LocatorAwareTrait;

$articles = $this->fetchTable('Articles');

// Start a new query.
$query = $articles->find();

在控制器内部，您可以使用使用约定系统创建的自动表变量

// Inside ArticlesController.php

$query = $this->Articles->find();

从表中选择行¶

use Cake\ORM\Locator\LocatorAwareTrait;

$query = $this->fetchTable('Articles')->find();

foreach ($query->all() as $article) {
    debug($article->title);
}

对于以下示例，假设 $articles 是一个 Cake\ORM\Table。在控制器内部，您可以使用 $this->Articles 而不是 $articles。

SelectQuery 对象中的几乎所有方法都将返回相同的查询，这意味着 SelectQuery 对象是延迟的，并且除非您告诉它们，否则不会执行

$query->where(['id' => 1]); // Return the same query object
$query->orderBy(['title' => 'DESC']); // Still same object, no SQL executed

当然，您可以将您在 SelectQuery 对象上调用的方法链接起来

$query = $articles
    ->find()
    ->select(['id', 'name'])
    ->where(['id !=' => 1])
    ->orderBy(['created' => 'DESC']);

foreach ($query->all() as $article) {
    debug($article->created);
}

如果您尝试在 SelectQuery 对象上调用 debug()，您将看到它的内部状态和将在数据库中执行的 SQL

debug($articles->find()->where(['id' => 1]));

// Outputs
// ...
// 'sql' => 'SELECT * FROM articles where id = ?'
// ...

您可以直接执行查询，而无需在查询上使用 foreach。最简单的方法是调用 all() 或 toList() 方法

$resultsIteratorObject = $articles
    ->find()
    ->where(['id >' => 1])
    ->all();

foreach ($resultsIteratorObject as $article) {
    debug($article->id);
}

$resultsArray = $articles
    ->find()
    ->where(['id >' => 1])
    ->all()
    ->toList();

foreach ($resultsArray as $article) {
    debug($article->id);
}

debug($resultsArray[0]->title);

在上面的示例中，$resultsIteratorObject 将是 Cake\ORM\ResultSet 的实例，您可以迭代并对其应用多种提取和遍历方法的对象。

通常，没有必要调用 all()，您只需迭代 SelectQuery 对象即可获取其结果。查询对象也可以直接用作结果对象；尝试迭代查询，调用 toList() 或 toArray()，将导致执行查询并将结果返回给您。

从表中选择单行¶

您可以使用 first() 方法获取查询中的第一个结果

$article = $articles
    ->find()
    ->where(['id' => 1])
    ->first();

debug($article->title);

从列中获取值列表¶

// Use the extract() method from the collections library
// This executes the query as well
$allTitles = $articles->find()->all()->extract('title');

foreach ($allTitles as $title) {
    echo $title;
}

您还可以从查询结果中获取键值列表

$list = $articles->find('list')->all();
foreach ($list as $id => $title) {
    echo "$id : $title"
}

有关如何自定义用于填充列表的字段的更多信息，请参阅查找键值对部分。

结果集是集合对象¶

一旦您熟悉了 Query 对象方法，强烈建议您访问集合部分，以提高您有效遍历结果的技能。结果集（通过调用 SelectQuery 的 all() 方法返回）实现了集合接口

// Use the combine() method from the collections library
// This is equivalent to find('list')
$keyValueList = $articles->find()->all()->combine('id', 'title');

// An advanced example
$results = $articles->find()
    ->where(['id >' => 1])
    ->orderBy(['title' => 'DESC'])
    ->all()
    ->map(function ($row) {
        $row->trimmedTitle = trim($row->title);

        return $row;
    })
    ->combine('id', 'trimmedTitle') // combine() is another collection method
    ->toArray(); // Also a collections library method

foreach ($results as $id => $trimmedTitle) {
    echo "$id : $trimmedTitle";
}

查询是延迟评估的¶

查询对象是延迟评估的。这意味着查询直到发生以下情况之一才会执行

使用 foreach 迭代查询。
调用查询的 execute() 方法。这将返回底层语句对象，并与插入/更新/删除查询一起使用。
调用查询的 first() 方法。这将返回由 SELECT 构建的集合中的第一个结果（它向查询添加 LIMIT 1）。
调用查询的 all() 方法。这将返回结果集，只能与 SELECT 语句一起使用。
调用查询的 toList() 或 toArray() 方法。

在满足这些条件之一之前，可以修改查询，而不会向数据库发送额外的 SQL。这也意味着如果查询尚未评估，则永远不会向数据库发送 SQL。一旦执行，修改和重新评估查询将导致运行额外的 SQL。多次调用同一个查询而不进行修改将返回相同的引用。

如果您想查看 CakePHP 生成的 SQL，您可以打开数据库查询日志记录。

选择数据¶

CakePHP 使构建 SELECT 查询变得简单。要限制获取的字段，您可以使用 select() 方法

$query = $articles->find();
$query->select(['id', 'title', 'body']);
foreach ($query->all() as $row) {
    debug($row->title);
}

您可以通过将字段作为关联数组提供来为字段设置别名

// Results in SELECT id AS pk, title AS aliased_title, body ...
$query = $articles->find();
$query->select(['pk' => 'id', 'aliased_title' => 'title', 'body']);

要选择不同的字段，您可以使用 distinct() 方法

// Results in SELECT DISTINCT country FROM ...
$query = $articles->find();
$query->select(['country'])
    ->distinct(['country']);

要设置一些基本条件，您可以使用 where() 方法

// Conditions are combined with AND
$query = $articles->find();
$query->where(['title' => 'First Post', 'published' => true]);

// You can call where() multiple times
$query = $articles->find();
$query->where(['title' => 'First Post'])
    ->where(['published' => true]);

您还可以将匿名函数传递给 where() 方法。传递的匿名函数将接收 \Cake\Database\Expression\QueryExpression 的实例作为其第一个参数，以及 \Cake\ORM\Query\SelectQuery 作为其第二个参数

$query = $articles->find();
$query->where(function (QueryExpression $exp, SelectQuery $q) {
    return $exp->eq('published', true);
});

请参阅高级条件部分，了解如何构建更复杂的 WHERE 条件。

选择特定字段¶

默认情况下，查询将选择表中的所有字段，例外情况是您自己调用 select() 函数并传递某些字段

// Only select id and title from the articles table
$articles->find()->select(['id', 'title']);

如果您希望在调用 select($fields) 之后仍然选择表中的所有字段，您可以将表实例传递给 select() 以实现此目的

// Only all fields from the articles table including
// a calculated slug field.
$query = $articlesTable->find();
$query
    ->select(['slug' => $query->func()->concat(['title' => 'identifier', '-', 'id' => 'identifier'])])
    ->select($articlesTable); // Select all fields from articles

您可以使用 selectAlso() 选择表中的所有字段，并同时选择一些其他字段。

$query = $articlesTable->find();
$query->selectAlso(['count' => $query->func()->count('*')]);

如果要选择表中除少数几个字段之外的所有字段，可以使用 selectAllExcept()

$query = $articlesTable->find();

// Get all fields except the published field.
$query->selectAllExcept($articlesTable, ['published']);

在使用包含关联时，还可以传递一个 Association 对象。

使用 SQL 函数¶

CakePHP 的 ORM 为一些常用的 SQL 函数提供了抽象。使用抽象允许 ORM 选择您想要的功能的平台特定实现。例如，concat 在 MySQL、PostgreSQL 和 SQL Server 中的实现不同。使用抽象可以让您的代码可移植。

// Results in SELECT COUNT(*) count FROM ...
$query = $articles->find();
$query->select(['count' => $query->func()->count('*')]);

请注意，大多数函数接受一个额外的参数来指定绑定到参数和/或返回类型的类型，例如

$query->select(['minDate' => $query->func()->min('date', ['date']);

有关详细信息，请参阅 Cake\Database\FunctionsBuilder 的文档。

您可以通过 SelectQuery::func() 访问几个 SQL 函数的现有包装器。

rand(): 通过 SQL 生成 0 到 1 之间的随机值。
sum(): 计算总和。假设参数是字面值。
avg(): 计算平均值。假设参数是字面值。
min(): 计算列的最小值。假设参数是字面值。
max(): 计算列的最大值。假设参数是字面值。
count(): 计算计数。假设参数是字面值。
cast(): 将字段或表达式从一种数据类型转换为另一种数据类型。
concat(): 将两个值连接在一起。假设参数是绑定参数。
coalesce(): 合并值。假设参数是绑定参数。
dateDiff(): 获取两个日期/时间之间的差异。假设参数是绑定参数。
now(): 默认返回日期和时间，但接受 'time' 或 'date' 以仅返回这些值。
extract(): 从 SQL 表达式中返回指定的日期部分。
dateAdd(): 将时间单位添加到日期表达式。
dayOfWeek(): 返回表示对 SQL WEEKDAY 函数的调用的 FunctionExpression。

仅窗口函数¶

这些仅窗口函数默认包含窗口表达式

rowNumber(): 返回 ROW_NUMBER() SQL 函数的 Aggregate 表达式。
lag(): 返回 LAG() SQL 函数的 Aggregate 表达式。
lead(): 返回 LEAD() SQL 函数的 Aggregate 表达式。

在为 SQL 函数提供参数时，您可以使用两种参数：字面量参数和绑定参数。标识符/字面量参数允许您引用列或其他 SQL 字面量。绑定参数可用于安全地将用户数据添加到 SQL 函数中。例如

$query = $articles->find()->innerJoinWith('Categories');
$concat = $query->func()->concat([
    'Articles.title' => 'identifier',
    ' - CAT: ',
    'Categories.name' => 'identifier',
    ' - Age: ',
    $query->func()->dateDiff([
        'NOW()' => 'literal',
        'Articles.created' => 'identifier',
    ])
]);
$query->select(['link_title' => $concat]);

literal 和 identifier 参数都允许您引用其他列和 SQL 字面量，而 identifier 如果启用了自动引用，将被适当地引用。如果未标记为字面量或标识符，参数将是绑定参数，允许您安全地将用户数据传递给函数。

上面的示例在 MYSQL 中生成类似以下内容。

SELECT CONCAT(
    Articles.title,
    :c0,
    Categories.name,
    :c1,
    (DATEDIFF(NOW(), Articles.created))
) FROM articles;

:c0 参数将在查询执行时绑定 ' - CAT:' 文本。 dateDiff 表达式被转换为相应的 SQL。

自定义函数¶

如果 func() 尚未包装您需要的 SQL 函数，则可以通过 func() 直接调用它，并像描述的那样安全地传递参数和用户数据。确保为自定义函数传递适当的参数类型，否则它们将被视为绑定参数。

$query = $articles->find();
$year = $query->func()->year([
    'created' => 'identifier'
]);
$time = $query->func()->date_format([
    'created' => 'identifier',
    "'%H:%i'" => 'literal'
]);
$query->select([
    'yearCreated' => $year,
    'timeCreated' => $time
]);

这些自定义函数将在 MYSQL 中生成类似以下内容

SELECT YEAR(created) as yearCreated,
       DATE_FORMAT(created, '%H:%i') as timeCreated
FROM articles;

注意

使用 func() 将不受信任的用户数据传递给任何 SQL 函数。

排序结果¶

要应用排序，可以使用 order 方法

$query = $articles->find()
    ->orderBy(['title' => 'ASC', 'id' => 'ASC']);

当在查询上多次调用 orderBy() 时，将追加多个子句。但是，在使用查找器时，您有时可能需要覆盖 ORDER BY。将 orderBy()（以及 orderByAsc() 或 orderByDesc()）的第二个参数设置为 SelectQuery::OVERWRITE 或 true

$query = $articles->find()
    ->orderBy(['title' => 'ASC']);
// Later, overwrite the ORDER BY clause instead of appending to it.
$query = $articles->find()
    ->orderBy(['created' => 'DESC'], SelectQuery::OVERWRITE);

当您需要对复杂表达式进行排序时，可以使用 orderByAsc 和 orderByDesc 方法。

$query = $articles->find();
$concat = $query->func()->concat([
    'title' => 'identifier',
    'synopsis' => 'identifier'
]);
$query->orderByAsc($concat);

要构建复杂的排序子句，请使用 Closure 来构建排序表达式

$query->orderByAsc(function (QueryExpression $exp, SelectQuery $query) {
    return $exp->addCase(/* ... */);
});

限制结果¶

要限制行数或设置行偏移量，可以使用 limit() 和 page() 方法

// Fetch rows 50 to 100
$query = $articles->find()
    ->limit(50)
    ->page(2);

如您从上面的示例中看到，所有修改查询的方法都提供了一个流畅的接口，允许您通过链式方法调用来构建查询。

聚合 - 分组和拥有¶

在使用聚合函数（如 count 和 sum）时，您可能希望使用 group by 和 having 子句。

$query = $articles->find();
$query->select([
    'count' => $query->func()->count('view_count'),
    'published_date' => 'DATE(created)'
])
->groupBy('published_date')
->having(['count >' => 3]);

Case 语句¶

ORM 还提供了 SQL case 表达式。 case 表达式允许在您的 SQL 中实现 if ... then ... else 逻辑。这对于报告您需要有条件地对数据进行求和或计数的数据，或者需要根据条件对特定数据进行计数的数据可能很有用。

如果我们想了解数据库中有多少已发布的文章，我们可以使用以下 SQL

SELECT
COUNT(CASE WHEN published = 'Y' THEN 1 END) AS number_published,
COUNT(CASE WHEN published = 'N' THEN 1 END) AS number_unpublished
FROM articles

要使用查询生成器执行此操作，我们将使用以下代码

$query = $articles->find();
$publishedCase = $query->newExpr()
    ->case()
    ->when(['published' => 'Y'])
    ->then(1);
$unpublishedCase = $query->newExpr()
    ->case()
    ->when(['published' => 'N'])
    ->then(1);

$query->select([
    'number_published' => $query->func()->count($publishedCase),
    'number_unpublished' => $query->func()->count($unpublishedCase)
]);

when() 方法接受 SQL 片段、数组条件和 Closure，以供您在需要额外的逻辑来构建 case 时使用。如果我们想根据人口规模将城市归类为 SMALL、MEDIUM 或 LARGE，我们可以执行以下操作

$query = $cities->find();
$sizing = $query->newExpr()->case()
    ->when(['population <' => 100000])
    ->then('SMALL')
    ->when($query->newExpr()->between('population', 100000, 999000))
    ->then('MEDIUM')
    ->when(['population >=' => 999001])
    ->then('LARGE');
$query = $query->select(['size' => $sizing]);
# SELECT CASE
#   WHEN population < 100000 THEN 'SMALL'
#   WHEN population BETWEEN 100000 AND 999000 THEN 'MEDIUM'
#   WHEN population >= 999001 THEN 'LARGE'
#   END AS size

在将用户提供的数据包含在 case 表达式中时，您需要小心，因为它会创建 SQL 注入漏洞。

// Unsafe do *not* use
$case->when($requestData['published']);

// Instead pass user data as values to array conditions
$case->when(['published' => $requestData['published']]);

对于更复杂的情况，您可以使用 QueryExpression 对象和绑定值

$userValue = $query->newExpr()
    ->case()
    ->when($query->newExpr('population >= :userData'))
    ->then(123, 'integer');

$query->select(['val' => $userValue])
    ->bind(':userData', $requestData['value'], 'integer');

通过使用绑定，您可以将用户数据安全地嵌入到复杂的原始 SQL 片段中。

then()、when() 和 else() 将尝试根据参数类型推断值类型。如果您需要将值绑定为不同的类型，您可以声明所需的类型

$case->when(['published' => true])->then('1', 'integer');

您可以通过使用 else() 创建 if ... then ... else 条件。

$published = $query->newExpr()
    ->case()
    ->when(['published' => true])
    ->then('Y');
    ->else('N');

# CASE WHEN published = true THEN 'Y' ELSE 'N' END;

此外，还可以通过将值传递给 case() 来创建简单变体

$published = $query->newExpr()
    ->case($query->identifier('published'))
    ->when(true)
    ->then('Y');
    ->else('N');

# CASE published WHEN true THEN 'Y' ELSE 'N' END;

addCase 函数还可以将多个语句链接在一起以创建 if .. then .. [elseif .. then .. ] [ .. else ] 逻辑在您的 SQL 中。

如果我们想根据人口规模将城市归类为 SMALL、MEDIUM 或 LARGE，我们可以执行以下操作

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->addCase(
            [
                $q->newExpr()->lt('population', 100000),
                $q->newExpr()->between('population', 100000, 999000),
                $q->newExpr()->gte('population', 999001),
            ],
            ['SMALL',  'MEDIUM', 'LARGE'], # values matching conditions
            ['string', 'string', 'string'] # type of each value
        );
    });
# WHERE CASE
#   WHEN population < 100000 THEN 'SMALL'
#   WHEN population BETWEEN 100000 AND 999000 THEN 'MEDIUM'
#   WHEN population >= 999001 THEN 'LARGE'
#   END

只要 case 条件少于值，addCase 就会自动生成一个 if .. then .. else 语句

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->addCase(
            [
                $q->newExpr()->eq('population', 0),
            ],
            ['DESERTED', 'INHABITED'], # values matching conditions
            ['string', 'string'] # type of each value
        );
    });
# WHERE CASE
#   WHEN population = 0 THEN 'DESERTED' ELSE 'INHABITED' END

获取数组而不是实体¶

虽然 ORM 和对象结果集很强大，但创建实体有时是不必要的。例如，在访问聚合数据时，构建实体可能没有意义。将数据库结果转换为实体的过程称为水合。如果您希望禁用此过程，可以执行以下操作

$query = $articles->find();
$query->enableHydration(false); // Results as arrays instead of entities
$result = $query->toList(); // Execute the query and return the array

执行完这些行后，您的结果应该类似于以下内容

[
    ['id' => 1, 'title' => 'First Article', 'body' => 'Article 1 body' ...],
    ['id' => 2, 'title' => 'Second Article', 'body' => 'Article 2 body' ...],
    ...
]

添加计算字段¶

在您的查询之后，您可能需要进行一些后处理。如果您需要添加一些计算字段或派生数据，可以使用 formatResults() 方法。这是一种对结果集进行映射的轻量级方法。如果您需要更多地控制该过程，或者想要减少结果，您应该使用 Map/Reduce 功能。如果您查询的是人员列表，您可以使用结果格式化程序来计算他们的年龄

// Assuming we have built the fields, conditions and containments.
$query->formatResults(function (\Cake\Collection\CollectionInterface $results) {
    return $results->map(function ($row) {
        $row['age'] = $row['birth_date']->diff(new \DateTime)->y;

        return $row;
    });
});

如您在上面的示例中看到，格式化回调将获得一个 ResultSetDecorator 作为其第一个参数。第二个参数将是格式化程序附加到的 Query 实例。 $results 参数可以根据需要遍历和修改。

结果格式化程序需要返回一个迭代器对象，该对象将用作查询的返回值。格式化程序函数在所有 Map/Reduce 例程执行后应用。结果格式化程序也可以从包含的关联中应用。CakePHP 将确保您的格式化程序被正确地作用域。例如，执行以下操作将按预期工作

// In a method in the Articles table
$query->contain(['Authors' => function ($q) {
    return $q->formatResults(function (\Cake\Collection\CollectionInterface $authors) {
        return $authors->map(function ($author) {
            $author['age'] = $author['birth_date']->diff(new \DateTime)->y;

            return $author;
        });
    });
}]);

// Get results
$results = $query->all();

// Outputs 29
echo $results->first()->author->age;

如上所述，附加到关联查询生成器的格式化程序的作用域仅限于操作关联中的数据。CakePHP 将确保计算值插入到正确的实体中。

如果您想用 formatResults 替换关联查找器结果，并且您的替换数据是关联数组，请使用 preserveKeys 在结果映射到父查询时保留键。例如

public function findSlugged(SelectQuery $query): SelectQuery
{
    return $query->applyOptions(['preserveKeys' => true])
        ->formatResults(function ($results) {
            return $results->indexBy(function ($record) {
                return Text::slug($record->name);
            });
        });
}

preserveKeys 选项也可以设置为包含选项。

在版本 5.1.0 中添加: 添加了 preserveKeys 选项。

高级条件¶

查询构建器使构建复杂的 where 子句变得简单。通过提供组合 where() 和表达式对象，可以表达分组条件。对于简单的查询，可以使用条件数组来构建条件

$query = $articles->find()
    ->where([
        'author_id' => 3,
        'OR' => [['view_count' => 2], ['view_count' => 3]],
    ]);

以上将生成类似于以下的 SQL

SELECT * FROM articles WHERE author_id = 3 AND (view_count = 2 OR view_count = 3)

如果您希望避免深度嵌套的数组，可以使用 where() 的回调形式来构建查询。回调接受一个 QueryExpression，允许您使用表达式构建器接口来构建更复杂的条件，而无需使用数组。例如

$query = $articles->find()->where(function (QueryExpression $exp, SelectQuery $query) {
    // Use add() to add multiple conditions for the same field.
    $author = $query->newExpr()->or(['author_id' => 3])->add(['author_id' => 2]);
    $published = $query->newExpr()->and(['published' => true, 'view_count' => 10]);

    return $exp->or([
        'promoted' => true,
        $query->newExpr()->and([$author, $published])
    ]);
});

以上生成的 SQL 类似于

SELECT *
FROM articles
WHERE (
    (
        (author_id = 2 OR author_id = 3)
        AND
        (published = 1 AND view_count = 10)
    )
    OR promoted = 1
)

传递给回调的 QueryExpression 允许您使用 **组合器** 和 **条件** 来构建完整的表达式。

组合器

这些创建新的 QueryExpression 对象，并设置添加到该表达式中的条件是如何组合在一起的。

and() 创建新的表达式对象，将所有条件与 AND 连接起来。
or() 创建新的表达式对象，将所有条件与 OR 连接起来。

条件

这些被添加到表达式中，并根据使用哪个组合器自动组合在一起。

传递给回调函数的 QueryExpression 默认设置为 and()

$query = $articles->find()
    ->where(function (QueryExpression $exp) {
        return $exp
            ->eq('author_id', 2)
            ->eq('published', true)
            ->notEq('spam', true)
            ->gt('view_count', 10);
    });

由于我们一开始就使用 where()，所以我们不需要调用 and()，因为这是隐式发生的。以上显示了一些新的条件方法与 AND 相结合。生成的 SQL 将如下所示

SELECT *
FROM articles
WHERE (
author_id = 2
AND published = 1
AND spam != 1
AND view_count > 10)

但是，如果我们想同时使用 AND 和 OR 条件，我们可以执行以下操作

$query = $articles->find()
    ->where(function (QueryExpression $exp) {
        $orConditions = $exp->or(['author_id' => 2])
            ->eq('author_id', 5);

        return $exp
            ->add($orConditions)
            ->eq('published', true)
            ->gte('view_count', 10);
    });

这将生成与以下类似的 SQL

SELECT *
FROM articles
WHERE (
    (author_id = 2 OR author_id = 5)
    AND published = 1
    AND view_count >= 10
)

**组合器** 还允许您传递一个回调，该回调将新的表达式对象作为参数，如果您想将方法链接分开

$query = $articles->find()
    ->where(function (QueryExpression $exp) {
        $orConditions = $exp->or(function (QueryExpression $or) {
            return $or->eq('author_id', 2)
                ->eq('author_id', 5);
        });

        return $exp
            ->not($orConditions)
            ->lte('view_count', 10);
    });

您可以使用 not() 来否定子表达式

$query = $articles->find()
    ->where(function (QueryExpression $exp) {
        $orConditions = $exp->or(['author_id' => 2])
            ->eq('author_id', 5);

        return $exp
            ->not($orConditions)
            ->lte('view_count', 10);
    });

这将生成以下看起来像这样的 SQL

SELECT *
FROM articles
WHERE (
    NOT (author_id = 2 OR author_id = 5)
    AND view_count <= 10
)

也可以使用 SQL 函数来构建表达式

$query = $articles->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        $year = $q->func()->year([
            'created' => 'identifier'
        ]);

        return $exp
            ->gte($year, 2014)
            ->eq('published', true);
    });

这将生成以下看起来像这样的 SQL

SELECT *
FROM articles
WHERE (
    YEAR(created) >= 2014
    AND published = 1
)

使用表达式对象时，可以使用以下方法来创建条件

eq() 创建一个相等条件

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->eq('population', '10000');
    });
# WHERE population = 10000

notEq() 创建一个不相等条件

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->notEq('population', '10000');
    });
# WHERE population != 10000

like() 使用 LIKE 运算符创建一个条件

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->like('name', '%A%');
    });
# WHERE name LIKE "%A%"

notLike() 创建一个否定的 LIKE 条件

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->notLike('name', '%A%');
    });
# WHERE name NOT LIKE "%A%"

in() 使用 IN 创建一个条件

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->in('country_id', ['AFG', 'USA', 'EST']);
    });
# WHERE country_id IN ('AFG', 'USA', 'EST')

notIn() 使用 IN 创建一个否定的条件

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->notIn('country_id', ['AFG', 'USA', 'EST']);
    });
# WHERE country_id NOT IN ('AFG', 'USA', 'EST')

gt() 创建一个 > 条件

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->gt('population', '10000');
    });
# WHERE population > 10000

gte() 创建一个 >= 条件

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->gte('population', '10000');
    });
# WHERE population >= 10000

lt() 创建一个 < 条件

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->lt('population', '10000');
    });
# WHERE population < 10000

lte() 创建一个 <= 条件

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->lte('population', '10000');
    });
# WHERE population <= 10000

isNull() 创建一个 IS NULL 条件

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->isNull('population');
    });
# WHERE (population) IS NULL

isNotNull() 创建一个否定的 IS NULL 条件

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->isNotNull('population');
    });
# WHERE (population) IS NOT NULL

between() 创建一个 BETWEEN 条件

$query = $cities->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->between('population', 999, 5000000);
    });
# WHERE population BETWEEN 999 AND 5000000,

exists() 使用 EXISTS 创建一个条件

$subquery = $cities->find()
    ->select(['id'])
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->equalFields('countries.id', 'cities.country_id');
    })
    ->andWhere(['population >' => 5000000]);

$query = $countries->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) use ($subquery) {
        return $exp->exists($subquery);
    });
# WHERE EXISTS (SELECT id FROM cities WHERE countries.id = cities.country_id AND population > 5000000)

notExists() 使用 EXISTS 创建一个否定的条件

$subquery = $cities->find()
    ->select(['id'])
    ->where(function (QueryExpression $exp, SelectQuery $q) {
        return $exp->equalFields('countries.id', 'cities.country_id');
    })
    ->andWhere(['population >' => 5000000]);

$query = $countries->find()
    ->where(function (QueryExpression $exp, SelectQuery $q) use ($subquery) {
        return $exp->notExists($subquery);
    });
# WHERE NOT EXISTS (SELECT id FROM cities WHERE countries.id = cities.country_id AND population > 5000000)

表达式对象应该涵盖许多常用的函数和表达式。如果您发现无法使用表达式创建所需的条件，您可以尝试使用 bind() 将参数手动绑定到条件中

$query = $cities->find()
    ->where([
        'start_date BETWEEN :start AND :end',
    ])
    ->bind(':start', '2014-01-01', 'date')
    ->bind(':end',   '2014-12-31', 'date');

在您无法获取或不想使用构建器方法来创建所需条件的情况下，您也可以在 where 子句中使用 SQL 代码片段

// Compare two fields to each other
$query->where(['Categories.parent_id != Parents.id']);

警告

表达式中使用的字段名称和 SQL 代码片段 **永远不要** 包含不可信的内容，因为您将创建 SQL 注入向量。有关如何安全地将不安全数据包含到函数调用中的信息，请参见使用 SQL 函数部分。

在表达式中使用标识符¶

当您需要在查询中引用列或 SQL 标识符时，可以使用 identifier() 方法

$query = $countries->find();
$query->select([
        'year' => $query->func()->year([$query->identifier('created')])
    ])
    ->where(function ($exp, $query) {
        return $exp->gt('population', 100000);
    });

您也可以在与聚合的比较中使用 identifier()

$query = $this->Orders->find();
$query->select(['Customers.customer_name', 'total_orders' => $query->func()->count('Orders.order_id')])
    ->contain('Customers')
    ->groupBy(['Customers.customer_name'])
    ->having(['total_orders >=' => $query->identifier('Customers.minimum_order_count')]);

警告

为了防止 SQL 注入，标识符表达式永远不要传入不可信数据。

排序规则¶

在您需要处理带重音符号的字符、多语言数据或区分大小写的比较的情况下，可以使用 IdentifierExpression 或 StringExpression 的 $collation 参数将字符表达式应用于特定的排序规则

use Cake\Database\Expression\IdentifierExpression;

$collation = 'Latin1_general_CI_AI'; //sql server example
$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) use ($collation) {
        return $exp->like(new IdentifierExpression('name', $collation), '%São José%');
    });
# WHERE name COLLATE LIKE Latin1_general_CI_AI "%São José%"

自动创建 IN 子句¶

使用 ORM 构建查询时，您通常不必指定您正在交互的列的数据类型，因为 CakePHP 可以根据模式数据推断出类型。如果在您的查询中，您希望 CakePHP 自动将相等转换为 IN 比较，您需要指定列数据类型

$query = $articles->find()
    ->where(['id' => $ids], ['id' => 'integer[]']);

// Or include IN to automatically cast to an array.
$query = $articles->find()
    ->where(['id IN' => $ids]);

以上将自动创建 id IN (...) 而不是 id = ?。当您不知道将获得标量值还是参数数组时，这将非常有用。任何数据类型名称后面的 [] 后缀表示查询构建器应将数据处理为数组。如果数据不是数组，它将首先被强制转换为数组。之后，数组中的每个值都将使用类型系统强制转换为类型。这适用于复杂类型。例如，您可以使用以下方法获取 DateTime 对象列表

$query = $articles->find()
    ->where(['post_date' => $dates], ['post_date' => 'date[]']);

自动创建 IS NULL¶

当条件值预期为 null 或任何其他值时，可以使用 IS 运算符自动创建正确的表达式

$query = $categories->find()
    ->where(['parent_id IS' => $parentId]);

以上将生成 parent_id = :c1 或 parent_id IS NULL，具体取决于 $parentId 的类型

自动创建 IS NOT NULL¶

当条件值预期不为 null 或任何其他值时，可以使用 IS NOT 运算符自动创建正确的表达式

$query = $categories->find()
    ->where(['parent_id IS NOT' => $parentId]);

以上将生成 parent_id != :c1 或 parent_id IS NOT NULL，具体取决于 $parentId 的类型

原始表达式¶

当您无法使用查询构建器构建所需的 SQL 时，可以使用表达式对象将 SQL 代码片段添加到查询中

$query = $articles->find();
$expr = $query->newExpr()->add('1 + 1');
$query->select(['two' => $expr]);

Expression 对象可以与任何查询构建器方法一起使用，例如 where()、limit()、groupBy()、select() 以及许多其他方法。

警告

使用表达式对象会导致您容易受到 SQL 注入的攻击。您永远不要在表达式中使用不可信数据。

使用连接角色¶

如果您在应用程序中配置了读写连接，您可以使用其中一个角色方法在 read 连接上运行查询

// Run a query on the read connection
$query->useReadRole();

// Run a query on the write connection (default)
$query->useWriteRole();

在 4.5.0 版本中添加：查询角色方法在 4.5.0 中添加

表达式连接¶

可以使用 setConjunction 方法更改用于连接查询表达式中条件的连接符

$query = $articles->find();
$expr = $query->newExpr(['1','1'])->setConjunction('+');
$query->select(['two' => $expr]);

并且可以与聚合一起使用

$query = $products->find();
$query->select(function ($query) {
        $stockQuantity = $query->func()->sum('Stocks.quantity');
        $totalStockValue = $query->func()->sum(
                $query->newExpr(['Stocks.quantity', 'Products.unit_price'])
                    ->setConjunction('*')
        );

        return [
            'Products.name',
            'stock_quantity' => $stockQuantity,
            'Products.unit_price',
            'total_stock_value' => $totalStockValue
        ];
    })
    ->innerJoinWith('Stocks')
    ->groupBy(['Products.id', 'Products.name', 'Products.unit_price']);

元组比较¶

元组比较涉及逐元素比较两行数据（元组），通常使用比较运算符，如 <, >, =

$products->find()
    ->where([
        'OR' => [
            ['unit_price <' => 20],
            ['unit_price' => 20, 'tax_percentage <=' => 5],
        ]
    ]);

# WHERE (unit_price < 20 OR (unit_price = 20 AND tax_percentage <= 5))

可以使用 TupleComparison 达到相同的结果

use Cake\Database\Expression\TupleComparison;

$products->find()
    ->where(
        new TupleComparison(
            ['unit_price', 'tax_percentage'],
            [20, 5],
            ['integer', 'integer'], # type of each value
            '<='
        )
    );

# WHERE (unit_price, tax_percentage) <= (20, 5))

元组比较也可以与 IN 一起使用，即使在不支持它的 DBMS 上也可以转换结果

$articles->find()
        ->where(
            new TupleComparison(
                ['articles.id', 'articles.author_id'],
                [[10, 10], [30, 10]],
                ['integer', 'integer'],
                'IN'
            ),
        );

# WHERE (1) = ( SELECT (1) WHERE ( ( articles.id = : 10 AND articles.author_id = : 10 ) OR ( articles.id = : 30 AND articles.author_id = : 30 ) ) )

注意

元组比较转换仅支持 IN 和 = 运算符

获取结果¶

完成查询后，您需要从中检索行。有几种方法可以做到这一点

// Iterate the query
foreach ($query as $row) {
    // Do stuff.
}

// Get the results
$results = $query->all();

您可以对查询对象使用任何集合方法来预处理或转换结果

// Use one of the collection methods.
$ids = $query->map(function ($row) {
    return $row->id;
});

$maxAge = $query->max(function ($max) {
    return $max->age;
});

您可以使用 first 或 firstOrFail 来检索单个记录。这些方法会更改查询，添加一个 LIMIT 1 子句

// Get just the first row
$row = $query->first();

// Get the first row or an exception.
$row = $query->firstOrFail();

返回记录的总数¶

使用单个查询对象，可以获取为一组条件找到的行总数

$total = $articles->find()->where(['is_active' => true])->count();

count() 方法会忽略 limit、offset 和 page 子句，因此以下将返回相同的结果

$total = $articles->find()->where(['is_active' => true])->limit(10)->count();

这在您需要提前知道总结果集大小但又不想构建另一个 SelectQuery 对象时非常有用。同样，在使用 count() 方法时，所有结果格式化和映射还原例程都会被忽略。

此外，还可以返回包含 group by 子句的查询的总数，而无需以任何方式重写查询。例如，考虑此查询，用于检索文章 ID 及其评论数

$query = $articles->find();
$query->select(['Articles.id', $query->func()->count('Comments.id')])
    ->matching('Comments')
    ->groupBy(['Articles.id']);
$total = $query->count();

计数后，该查询仍可用于获取相关记录

$list = $query->all();

有时，您可能希望为计算查询的总记录数提供替代方法。此操作的一个常见用例是提供缓存值或对总行的估计，或修改查询以删除诸如左联接之类的昂贵且不必要的部件。这在使用 CakePHP 内置的分页系统（它会调用 count() 方法）时特别有用

$query = $query->where(['is_active' => true])->counter(function ($query) {
    return 100000;
});
$query->count(); // Returns 100000

在上面的示例中，当分页组件调用 count 方法时，它将接收估计的硬编码行数。

缓存加载的结果¶

在获取不经常更改的实体时，您可能希望缓存结果。 SelectQuery 类使此操作变得简单

$query->cache('recent_articles');

将启用对查询结果集的缓存。如果仅向 cache() 提供一个参数，则将使用“默认”缓存配置。您可以使用第二个参数控制使用哪个缓存配置

// String config name.
$query->cache('recent_articles', 'dbResults');

// Instance of CacheEngine
$query->cache('recent_articles', $memcache);

除了支持静态密钥之外，cache() 方法还接受一个函数来生成密钥。您提供的函数将接收查询作为参数。然后，您可以读取查询的各个方面以动态生成缓存密钥

// Generate a key based on a simple checksum
// of the query's where clause
$query->cache(function ($q) {
    return 'articles-' . md5(serialize($q->clause('where')));
});

cache 方法使向自定义查找器或通过事件侦听器添加缓存结果变得简单。

获取缓存查询的结果时，会发生以下情况

如果查询有结果集，则将返回这些结果。
将解析缓存密钥并读取缓存数据。如果缓存数据不为空，则将返回这些结果。
如果缓存未命中，则将执行查询，将触发 Model.beforeFind 事件，并将创建一个新的 ResultSet。此 ResultSet 将被写入缓存并返回。

注意

您无法缓存流式查询结果。

加载关联¶

构建器可以帮助您以最少的查询次数从多个表中检索数据。要能够获取关联数据，您首先需要按照关联 - 将表链接在一起部分中的说明设置表之间的关联。这种将查询组合起来以从其他表中获取关联数据的技术称为渴望加载。

渴望加载有助于避免与 ORM 中延迟加载相关的许多潜在性能问题。渴望加载生成的查询可以更好地利用联接，从而使查询更高效。在 CakePHP 中，您可以使用“contain”方法指定要渴望加载的关联

// In a controller or table method.

// As an option to find()
$query = $articles->find('all', contain: ['Authors', 'Comments']);

// As a method on the query object
$query = $articles->find('all');
$query->contain(['Authors', 'Comments']);

以上将为结果集中的每篇文章加载相关的作者和评论。您可以使用嵌套数组来定义要加载的关联，以加载嵌套关联

$query = $articles->find()->contain([
    'Authors' => ['Addresses'], 'Comments' => ['Authors']
]);

或者，您可以使用点表示法来表达嵌套关联

$query = $articles->find()->contain([
    'Authors.Addresses',
    'Comments.Authors'
]);

您可以根据需要加载任意深度的关联

$query = $products->find()->contain([
    'Shops.Cities.Countries',
    'Shops.Managers'
]);

这等效于调用

$query = $products->find()->contain([
    'Shops' => ['Cities.Countries', 'Managers']
]);

您可以使用多个 contain() 语句从所有关联中选择字段

$query = $this->find()->select([
    'Realestates.id',
    'Realestates.title',
    'Realestates.description'
])
->contain([
    'RealestateAttributes' => [
        'Attributes' => [
            'fields' => [
                // Aliased fields in contain() must include
                // the model prefix to be mapped correctly.
                'Attributes__name' => 'attr_name',
            ],
        ],
    ],
])
->contain([
    'RealestateAttributes' => [
        'fields' => [
            'RealestateAttributes.realestate_id',
            'RealestateAttributes.value',
        ],
    ],
])
->where($condition);

如果需要重置查询上的包含关系，可以将第二个参数设置为 true

$query = $articles->find();
$query->contain(['Authors', 'Comments'], true);

注意

contain() 调用中的关联名称应与关联定义中的关联大小写相同，而不是用于保存关联记录的属性名称。例如，如果将关联声明为 belongsTo('Users')，则必须使用 contain('Users')，而不是 contain('users') 或 contain('user')。

将条件传递给包含¶

使用 contain() 时，您可以限制关联返回的数据并按条件对其进行过滤。要指定条件，请传递一个匿名函数，该函数接收作为第一个参数的查询对象 \Cake\ORM\Query\SelectQuery

// In a controller or table method.
$query = $articles->find()->contain('Comments', function (SelectQuery $q) {
    return $q
        ->select(['body', 'author_id'])
        ->where(['Comments.approved' => true]);
});

这在控制器级别的分页中也有效

$this->paginate['contain'] = [
    'Comments' => function (SelectQuery $query) {
        return $query->select(['body', 'author_id'])
        ->where(['Comments.approved' => true]);
    }
];

警告

如果结果缺少关联实体，请确保在查询中选择了外键列。没有外键，ORM 就无法找到匹配的行。

也可以使用点表示法限制深度嵌套的关联

$query = $articles->find()->contain([
    'Comments',
    'Authors.Profiles' => function (SelectQuery $q) {
        return $q->where(['Profiles.is_published' => true]);
    }
]);

在上面的示例中，即使作者没有已发布的资料，您仍然会获得作者。要仅获取具有已发布资料的作者，请使用匹配()。如果在关联中定义了自定义查找器，您可以在 contain() 中使用它们

// Bring all articles, but only bring the comments that are approved and
// popular.
$query = $articles->find()->contain('Comments', function (SelectQuery $q) {
    return $q->find('approved')->find('popular');
});

注意

对于 BelongsTo 和 HasOne 关联，只有 select 和 where 子句在 contain() 查询中有效。对于 HasMany 和 BelongsToMany，所有子句（如 order()）都是有效的。

您可以控制 contain() 使用的查询子句，而不仅仅是控制查询子句。如果传递带有关联的数组，则可以覆盖 foreignKey、joinType 和 strategy。有关每种关联类型的默认值和选项的详细信息，请参阅关联 - 将表链接在一起。

您可以传递 false 作为新的 foreignKey 来完全禁用外键约束。使用 queryBuilder 选项在使用数组时自定义查询

$query = $articles->find()->contain([
    'Authors' => [
        'foreignKey' => false,
        'queryBuilder' => function (SelectQuery $q) {
            return $q->where(/* ... */); // Full conditions for filtering
        }
    ]
]);

如果已使用 select() 限制了要加载的字段，但还想从包含的关联中加载字段，可以将关联对象传递给 select()

// Select id & title from articles, but all fields off of Users.
$query = $articles->find()
    ->select(['id', 'title'])
    ->select($articles->Users)
    ->contain(['Users']);

或者，您可以在匿名函数中使用 enableAutoFields()

// Select id & title from articles, but all fields off of Users.
$query = $articles->find()
    ->select(['id', 'title'])
    ->contain(['Users' => function(SelectQuery $q) {
        return $q->enableAutoFields();
    }]);

对包含的关联进行排序¶

在加载 HasMany 和 BelongsToMany 关联时，您可以使用 sort 选项对这些关联中的数据进行排序。

$query->contain([
    'Comments' => [
        'sort' => ['Comments.created' => 'DESC']
    ]
]);

通过关联数据进行过滤¶

关联的常见查询案例是查找与特定关联数据“匹配”的记录。例如，如果您有“文章属于多个标签”，您可能希望找到带有 CakePHP 标签的文章。使用 CakePHP 中的 ORM 完成此操作非常简单。

// In a controller or table method.

$query = $articles->find();
$query->matching('Tags', function ($q) {
    return $q->where(['Tags.name' => 'CakePHP']);
});

您也可以将此策略应用于 HasMany 关联。例如，如果“作者拥有多个文章”，您可以使用以下方法查找所有拥有近期发布文章的作者。

$query = $authors->find();
$query->matching('Articles', function ($q) {
    return $q->where(['Articles.created >=' => new DateTime('-10 days')]);
});

通过深度关联进行过滤使用与 contain() 相同的可预测语法。

// In a controller or table method.
$query = $products->find()->matching(
    'Shops.Cities.Countries', function ($q) {
        return $q->where(['Countries.name' => 'Japan']);
    }
);

// Bring unique articles that were commented by `markstory` using passed variable
// Dotted matching paths should be used over nested matching() calls
$username = 'markstory';
$query = $articles->find()->matching('Comments.Users', function ($q) use ($username) {
    return $q->where(['username' => $username]);
});

注意

由于此函数将创建一个 INNER JOIN，您可能希望考虑在查找查询中调用 distinct，因为如果您的条件没有排除重复行，您可能会得到重复行。例如，当同一个用户对同一篇文章评论多次时，可能会出现这种情况。

与“匹配”的关联中的数据将在实体的 _matchingData 属性中可用。如果匹配和包含相同的关联，您可以在结果中获得 _matchingData 和标准关联属性。

使用 innerJoinWith¶

有时您需要匹配特定关联数据，但无需像 matching() 一样实际加载匹配的记录。您可以使用 innerJoinWith() 创建 matching() 使用的 INNER JOIN。

$query = $articles->find();
$query->innerJoinWith('Tags', function ($q) {
    return $q->where(['Tags.name' => 'CakePHP']);
});

innerJoinWith() 允许您使用相同的参数和点表示法。

$query = $products->find()->innerJoinWith(
    'Shops.Cities.Countries', function ($q) {
        return $q->where(['Countries.name' => 'Japan']);
    }
);

当您想要匹配特定记录并一起加载关联数据时，可以将 innerJoinWith() 和 contain() 与相同的关联组合使用。以下示例匹配具有特定标签的文章，并加载相同的标签。

$filter = ['Tags.name' => 'CakePHP'];
$query = $articles->find()
    ->distinct($articles->getPrimaryKey())
    ->contain('Tags', function (SelectQuery $q) use ($filter) {
        return $q->where($filter);
    })
    ->innerJoinWith('Tags', function (SelectQuery $q) use ($filter) {
        return $q->where($filter);
    });

注意

如果您使用 innerJoinWith() 并希望从该关联中 select() 字段，您需要使用该字段的别名。

$query
    ->select(['country_name' => 'Countries.name'])
    ->innerJoinWith('Countries');

如果您不使用别名，您将在 _matchingData 中看到上面 matching() 描述的数据。这是 matching() 不知道您手动选择了该字段的边缘情况。

警告

您不应该将 innerJoinWith() 和 matching() 与相同的关联组合使用。这将产生多个 INNER JOIN 语句，并且可能不会创建您预期的查询。

使用 notMatching¶

matching() 的反面是 notMatching()。此函数将更改查询，以便它过滤与指定关联无关的结果。

// In a controller or table method.

$query = $articlesTable
    ->find()
    ->notMatching('Tags', function ($q) {
        return $q->where(['Tags.name' => 'boring']);
    });

上面的示例将找到所有没有使用“无聊”一词标记的文章。您也可以将此方法应用于 HasMany 关联。例如，您可以找到在过去 10 天没有发布文章的所有作者。

$query = $authorsTable
    ->find()
    ->notMatching('Articles', function ($q) {
        return $q->where(['Articles.created >=' => new \DateTime('-10 days')]);
    });

您也可以使用此方法过滤掉与深度关联不匹配的记录。例如，您可以找到没有被特定用户评论的文章。

$query = $articlesTable
    ->find()
    ->notMatching('Comments.Users', function ($q) {
        return $q->where(['username' => 'jose']);
    });

由于没有评论的文章也满足上述条件，您可能希望在同一个查询中组合 matching() 和 notMatching()。以下示例将找到至少有一条评论，但没有被特定用户评论的文章。

$query = $articlesTable
    ->find()
    ->notMatching('Comments.Users', function ($q) {
        return $q->where(['username' => 'jose']);
    })
    ->matching('Comments');

注意

由于 notMatching() 将创建一个 LEFT JOIN，您可能希望考虑在查找查询中调用 distinct，因为否则您可能会得到重复行。

请记住，与 matching() 函数相反，notMatching() 不会在结果中的 _matchingData 属性中添加任何数据。

使用 leftJoinWith¶

在某些情况下，您可能希望根据关联计算结果，而无需加载它的所有记录。例如，如果您想加载文章的总评论数以及所有文章数据，可以使用 leftJoinWith() 函数。

$query = $articlesTable->find();
$query->select(['total_comments' => $query->func()->count('Comments.id')])
    ->leftJoinWith('Comments')
    ->groupBy(['Articles.id'])
    ->enableAutoFields(true);

上述查询的结果将包含文章数据以及每个文章的 total_comments 属性。

leftJoinWith() 也可以与深度嵌套的关联一起使用。这很有用，例如，用于获取每个作者标记有特定词的文章数量。

$query = $authorsTable
    ->find()
    ->select(['total_articles' => $query->func()->count('Articles.id')])
    ->leftJoinWith('Articles.Tags', function ($q) {
        return $q->where(['Tags.name' => 'awesome']);
    })
    ->groupBy(['Authors.id'])
    ->enableAutoFields(true);

此函数不会将指定关联中的任何列加载到结果集中。

添加联接¶

除了使用 contain() 加载相关数据外，您还可以使用查询构建器添加其他联接。

$query = $articles->find()
    ->join([
        'table' => 'comments',
        'alias' => 'c',
        'type' => 'LEFT',
        'conditions' => 'c.article_id = articles.id',
    ]);

您可以通过传递包含多个联接的关联数组来同时追加多个联接。

$query = $articles->find()
    ->join([
        'c' => [
            'table' => 'comments',
            'type' => 'LEFT',
            'conditions' => 'c.article_id = articles.id',
        ],
        'u' => [
            'table' => 'users',
            'type' => 'INNER',
            'conditions' => 'u.id = articles.user_id',
        ]
    ]);

如上所示，在添加联接时，别名可以是外部数组键。联接条件也可以表示为条件数组。

$query = $articles->find()
    ->join([
        'c' => [
            'table' => 'comments',
            'type' => 'LEFT',
            'conditions' => [
                'c.created >' => new DateTime('-5 days'),
                'c.moderated' => true,
                'c.article_id = articles.id'
            ]
        ],
    ], ['c.created' => 'datetime', 'c.moderated' => 'boolean']);

在手动创建联接并使用基于数组的条件时，您需要为联接条件中的每个列提供数据类型。通过为联接条件提供数据类型，ORM 可以正确地将数据类型转换为 SQL。除了 join() 之外，您还可以使用 rightJoin()、leftJoin() 和 innerJoin() 来创建联接。

// Join with an alias and string conditions
$query = $articles->find();
$query->leftJoin(
    ['Authors' => 'authors'],
    ['Authors.id = Articles.author_id']);

// Join with an alias, array conditions, and types
$query = $articles->find();
$query->innerJoin(
    ['Authors' => 'authors'],
    [
        'Authors.promoted' => true,
        'Authors.created' => new DateTime('-5 days'),
        'Authors.id = Articles.author_id',
    ],
    [
        'Authors.promoted' => 'boolean',
        'Authors.created' => 'datetime',
    ]
);

需要注意的是，如果您在定义 Connection 时将 quoteIdentifiers 选项设置为 true，则表字段之间的联接条件应如下设置。

$query = $articles->find()
    ->join([
        'c' => [
            'table' => 'comments',
            'type' => 'LEFT',
            'conditions' => [
                'c.article_id' => new \Cake\Database\Expression\IdentifierExpression('articles.id'),
            ],
        ],
    ]);

这确保了您所有的标识符将在整个查询中被引用，避免了某些数据库驱动程序（尤其是 PostgreSQL）的错误。

插入数据¶

与之前的示例不同，您不能使用 find() 来创建插入查询。相反，使用 insertQuery() 创建一个新的 InsertQuery 对象。

$query = $articles->insertQuery();
$query->insert(['title', 'body'])
    ->values([
        'title' => 'First post',
        'body' => 'Some body text',
    ])
    ->execute();

要使用一个查询插入多行，您可以根据需要多次链接 values() 方法。

$query = $articles->insertQuery();
$query->insert(['title', 'body'])
    ->values([
        'title' => 'First post',
        'body' => 'Some body text',
    ])
    ->values([
        'title' => 'Second post',
        'body' => 'Another body text',
    ])
    ->execute();

通常，使用实体和 Cake\ORM\Table::save() 插入数据更容易。通过将 SELECT 和 INSERT 查询组合在一起，您可以创建 INSERT INTO ... SELECT 样式的查询。

$select = $articles->find()
    ->select(['title', 'body', 'published'])
    ->where(['id' => 3]);

$query = $articles->insertQuery()
    ->insert(['title', 'body', 'published'])
    ->values($select)
    ->execute();

注意

使用查询构建器插入记录不会触发诸如 Model.afterSave 之类的事件。相反，您应该使用 ORM 保存数据。

更新数据¶

与插入查询一样，您不应该使用 find() 来创建更新查询。相反，使用 updateQuery() 创建一个新的 Query 对象。

$query = $articles->updateQuery();
$query->set(['published' => true])
    ->where(['id' => $id])
    ->execute();

通常，使用实体和 patchEntity() 更新数据更容易。

注意

使用查询构建器更新记录不会触发诸如 Model.afterSave 之类的事件。相反，您应该使用 ORM 保存数据。

删除数据¶

与插入查询一样，您不能使用 find() 来创建删除查询。相反，使用 deleteQuery() 创建一个新的查询对象。

$query = $articles->deleteQuery();
$query->where(['id' => $id])
    ->execute();

通常，使用实体和 Cake\ORM\Table::delete() 删除数据更容易。

SQL 注入防护¶

虽然 ORM 和数据库抽象层可以防止大多数 SQL 注入问题，但仍然可以通过不当使用使自己容易受到攻击。

使用条件数组时，键/左侧以及单个值条目不能包含用户数据。

$query->where([
    // Data on the key/left-hand side is unsafe, as it will be
    // inserted into the generated query as-is
    $userData => $value,

    // The same applies to single value entries, they are not
    // safe to use with user data in any form
    $userData,
    "MATCH (comment) AGAINST ($userData)",
    'created < NOW() - ' . $userData
]);

使用表达式构建器时，列名不能包含用户数据。

$query->where(function (QueryExpression $exp) use ($userData, $values) {
    // Column names in all expressions are not safe.
    return $exp->in($userData, $values);
});

构建函数表达式时，函数名不应该包含用户数据。

// Not safe.
$query->func()->{$userData}($arg1);

// Also not safe to use an array of
// user data in a function expression
$query->func()->coalesce($userData);

原始表达式从不安全。

$expr = $query->newExpr()->add($userData);
$query->select(['two' => $expr]);

绑定值¶

可以使用绑定来防止许多不安全情况。可以使用 Cake\Database\Query::bind() 方法将值绑定到查询。

以下示例是上面给出的不安全、容易遭受 SQL 注入攻击的示例的安全变体。

$query
    ->where([
        'MATCH (comment) AGAINST (:userData)',
        'created < NOW() - :moreUserData',
    ])
    ->bind(':userData', $userData, 'string')
    ->bind(':moreUserData', $moreUserData, 'datetime');

注意

与 Cake\Database\StatementInterface::bindValue() 不同，Query::bind() 需要传递包含冒号的命名占位符！

更复杂的查询¶

如果您的应用程序需要使用更复杂的查询，可以使用 ORM 查询构建器来表达许多复杂查询。

联合¶

联合通过将一个或多个 select 查询组合在一起创建。

$inReview = $articles->find()
    ->where(['need_review' => true]);

$unpublished = $articles->find()
    ->where(['published' => false]);

$unpublished->union($inReview);

可以使用 unionAll() 方法创建 UNION ALL 查询。

$inReview = $articles->find()
    ->where(['need_review' => true]);

$unpublished = $articles->find()
    ->where(['published' => false]);

$unpublished->unionAll($inReview);

交集¶

交集允许您将两个查询的结果集组合在一起，并查找具有重叠结果的结果。交集通过将一个或多个 select 查询组合在一起创建。

$inReview = $articles->find()
    ->where(['need_review' => true]);

$unpublished = $articles->find()
    ->where(['published' => false]);

$unpublished->intersect($inReview);

可以使用 intersectAll() 方法创建 INTERSECT ALL 查询。

$inReview = $articles->find()
    ->where(['need_review' => true]);

$unpublished = $articles->find()
    ->where(['published' => false]);

$unpublished->intersectAll($inReview);

版本 5.1.0 中新增: intersect() 和 intersectAll() 已添加。

子查询¶

子查询使您能够将查询组合在一起，并根据其他查询的结果构建条件和结果。

$matchingComment = $articles->getAssociation('Comments')->find()
    ->select(['article_id'])
    ->distinct()
    ->where(['comment LIKE' => '%CakePHP%']);

// Use a subquery to create conditions
$query = $articles->find()
    ->where(['id IN' => $matchingComment]);

// Join the results of a subquery into another query.
// Giving the subquery an alias provides a way to reference
// results in subquery.
$query = $articles->find();
$query->from(['matches' => $matchingComment])
    ->innerJoin(
        ['Articles' =>  'articles'],
        ['Articles.id' => $query->identifier('matches.id') ]
    );

子查询可以在可以使用查询表达式的任何地方接受。例如，在 select()、from() 和 join() 方法中。以上示例使用标准的 ORM\Query\SelectQuery 对象，它将生成别名，这些别名可能会使引用外部查询中的结果变得更加复杂。从 4.2.0 开始，您可以使用 Table::subquery() 创建一个专门的查询实例，该实例不会生成别名。

$comments = $articles->getAssociation('Comments')->getTarget();

$matchingComment = $comments->subquery()
    ->select(['article_id'])
    ->distinct()
    ->where(['comment LIKE' => '%CakePHP%']);

$query = $articles->find()
    ->where(['id IN' => $matchingComment]);

添加锁定语句¶

大多数关系数据库供应商支持在执行 select 操作时取出锁。您可以为此使用 epilog() 方法。

// In MySQL
$query->epilog('FOR UPDATE');

epilog() 方法允许您在查询末尾追加原始 SQL。您永远不应该将原始用户数据放入 epilog() 中。

窗口函数¶

窗口函数允许您使用与当前行相关的行执行计算。它们通常用于计算查询中部分行集的总计或偏移量。例如，如果我们想查找每篇文章最早和最晚评论的日期，我们可以使用窗口函数。

$query = $articles->find();
$query->select([
    'Articles.id',
    'Articles.title',
    'Articles.user_id'
    'oldest_comment' => $query->func()
        ->min('Comments.created')
        ->partition('Comments.article_id'),
    'latest_comment' => $query->func()
        ->max('Comments.created')
        ->partition('Comments.article_id'),
])
->innerJoinWith('Comments');

以上将生成类似于以下的 SQL：

SELECT
    Articles.id,
    Articles.title,
    Articles.user_id
    MIN(Comments.created) OVER (PARTITION BY Comments.article_id) AS oldest_comment,
    MAX(Comments.created) OVER (PARTITION BY Comments.article_id) AS latest_comment,
FROM articles AS Articles
INNER JOIN comments AS Comments

窗口表达式可以应用于大多数聚合函数。任何 Cake 在 FunctionsBuilder 中使用包装器抽象的聚合函数都将返回一个 AggregateExpression，它允许您附加窗口表达式。您可以通过 FunctionsBuilder::aggregate() 创建自定义聚合函数。

这些是最常用的窗口功能。大多数功能由 AggregateExpresion 提供，但请确保您遵循数据库文档中关于使用和限制的说明。

orderBy($fields) 以与查询 ORDER BY 相同的方式对聚合组进行排序。
partition($expressions) 根据列名向窗口添加一个或多个分区。
rows($start, $end) 定义应包含在聚合函数中的位于当前行之前和/或之后的行的偏移量。
range($start, $end) 定义应包含在聚合函数中的位于当前行之前和/或之后的行的值范围。这将根据 orderBy() 字段评估值。

如果您需要多次重复使用相同的窗口表达式，可以使用 window() 方法创建命名窗口。

$query = $articles->find();

// Define a named window
$query->window('related_article', function ($window, $query) {
    $window->partition('Comments.article_id');

    return $window;
});

$query->select([
    'Articles.id',
    'Articles.title',
    'Articles.user_id'
    'oldest_comment' => $query->func()
        ->min('Comments.created')
        ->over('related_article'),
    'latest_comment' => $query->func()
        ->max('Comments.created')
        ->over('related_article'),
]);

公用表表达式¶

公用表表达式或 CTE 在构建报告查询时非常有用，在这些查询中，您需要将多个较小的查询结果组合在一起。它们可以起到类似于数据库视图或子查询结果的作用。公用表表达式与派生表和视图的区别在于以下几点：

与视图不同，您不必维护公用表表达式的模式。模式隐式地基于表表达式的结果集。
您可以多次引用公用表表达式的结果，而不会像子查询连接那样产生性能损失。

例如，让我们获取客户列表及其每个客户下的订单数量。在 SQL 中，我们将使用：

WITH orders_per_customer AS (
    SELECT COUNT(*) AS order_count, customer_id FROM orders GROUP BY customer_id
)
SELECT name, orders_per_customer.order_count
FROM customers
INNER JOIN orders_per_customer ON orders_per_customer.customer_id = customers.id

要使用 ORM 查询构建器构建该查询，我们将使用：

// Start the final query
$query = $this->Customers->find();

// Attach a common table expression
$query->with(function ($cte) {
    // Create a subquery to use in our table expression
    $q = $this->Orders->subquery();
    $q->select([
        'order_count' => $q->func()->count('*'),
        'customer_id',
    ])
    ->groupBy('customer_id');

    // Attach the new query to the table expression
    return $cte
        ->name('orders_per_customer')
        ->query($q);
});

// Finish building the final query
$query->select([
    'name',
    'order_count' => 'orders_per_customer.order_count',
])
->join([
    // Define the join with our table expression
    'orders_per_customer' => [
        'table' => 'orders_per_customer',
        'conditions' => 'orders_per_customer.customer_id = Customers.id',
    ],
]);

如果您需要构建递归查询（WITH RECURSIVE …），请在 return $cte 上链接 ->recursive()。

执行复杂查询¶

虽然查询构建器通过构建器方法使大多数查询成为可能，但非常复杂的查询可能难以构建。您可能希望直接执行所需的 SQL。

直接执行 SQL 使您能够微调要运行的查询。但是，这样做不允许您使用 contain 或其他更高级别的 ORM 功能。

改进此文档

页面内容

查询构建器¶

SelectQuery 对象¶

从表中选择行¶

从表中选择单行¶

从列中获取值列表¶

结果集是集合对象¶

查询是延迟评估的¶

选择数据¶

选择特定字段¶

使用 SQL 函数¶

仅窗口函数¶

自定义函数¶

排序结果¶

限制结果¶

聚合 - 分组和拥有¶

Case 语句¶

获取数组而不是实体¶

添加计算字段¶

高级条件¶

在表达式中使用标识符¶

排序规则¶

自动创建 IN 子句¶

自动创建 IS NULL¶

自动创建 IS NOT NULL¶

原始表达式¶

使用连接角色¶

表达式连接¶

元组比较¶

获取结果¶

返回记录的总数¶

缓存加载的结果¶

加载关联¶

将条件传递给包含¶

对包含的关联进行排序¶

通过关联数据进行过滤¶

使用 innerJoinWith¶

使用 notMatching¶

使用 leftJoinWith¶

添加联接¶

插入数据¶

更新数据¶

删除数据¶

SQL 注入防护¶

绑定值¶

更复杂的查询¶

联合¶

交集¶

子查询¶

添加锁定语句¶

窗口函数¶

公用表表达式¶

执行复杂查询¶