7 个关注者

查询构建器 ¶

构建查询
查询方法

查询构建器建立在数据库访问对象的基础上，允许您以编程方式和与 DBMS 无关的方式构建 SQL 查询。与编写原始 SQL 语句相比，使用查询构建器将帮助您编写更易读的 SQL 相关代码并生成更安全的 SQL 语句。

使用查询构建器通常涉及两个步骤

构建一个 yii\db\Query 对象来表示 SELECT SQL 语句的不同部分（例如 SELECT、FROM）。
执行 yii\db\Query 的一个查询方法（例如 all()）从数据库中检索数据。

以下代码展示了一种使用查询构建器的典型方式

$rows = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->limit(10)
    ->all();

上面的代码生成了以下 SQL 查询并执行，其中 :last_name 参数绑定到字符串 'Smith'。

SELECT `id`, `email` 
FROM `user`
WHERE `last_name` = :last_name
LIMIT 10

信息: 您通常主要使用 yii\db\Query 而不是 yii\db\QueryBuilder。当您调用其中一个查询方法时，后者会由前者隐式调用。 yii\db\QueryBuilder 是一个负责从与 DBMS 无关的 yii\db\Query 对象生成依赖于 DBMS 的 SQL 语句（例如，以不同方式引用表/列名称）的类。

构建查询 ¶

要构建一个 yii\db\Query 对象，您调用不同的查询构建方法来指定 SQL 查询的不同部分。这些方法的名称类似于在 SQL 语句的相应部分中使用的 SQL 关键字。例如，要指定 SQL 查询的 FROM 部分，您将调用 from() 方法。所有查询构建方法都返回查询对象本身，这允许您将多个调用链接在一起。

在下面，我们将描述每个查询构建方法的使用。

select() ¶

The select() 方法指定 SQL 语句的 SELECT 部分。您可以使用数组或字符串来指定要选择的列，如下所示。当从查询对象生成 SQL 语句时，所选择的列名将被自动引用。

$query->select(['id', 'email']);

// equivalent to:

$query->select('id, email');

所选择的列名可能包括表前缀和/或列别名，就像您在编写原始 SQL 查询时一样。例如，

$query->select(['user.id AS user_id', 'email']);

// equivalent to:

$query->select('user.id AS user_id, email');

如果您使用数组格式指定列，您也可以使用数组键来指定列别名。例如，上面的代码可以改写如下：

$query->select(['user_id' => 'user.id', 'email']);

如果您在构建查询时没有调用 select() 方法，则将选择 *，这意味着选择所有列。

除了列名之外，您还可以选择 DB 表达式。当选择包含逗号的 DB 表达式时，您必须使用数组格式，以避免错误的自动名称引用。例如：

$query->select(["CONCAT(first_name, ' ', last_name) AS full_name", 'email']);

与所有涉及原始 SQL 的地方一样，您可以在 select 中编写 DB 表达式时，使用 DBMS 无关的引用语法来引用表名和列名。

从 2.0.1 版本开始，您也可以选择子查询。您应该使用 yii\db\Query 对象来指定每个子查询。例如：

$subQuery = (new Query())->select('COUNT(*)')->from('user');

// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post`
$query = (new Query())->select(['id', 'count' => $subQuery])->from('post');

要选择不同的行，您可以调用 distinct()，如下所示

// SELECT DISTINCT `user_id` ...
$query->select('user_id')->distinct();

您可以调用 addSelect() 来选择额外的列。例如：

$query->select(['id', 'username'])
    ->addSelect(['email']);

from() ¶

from() 方法指定 SQL 语句的 FROM 片段。例如：

// SELECT * FROM `user`
$query->from('user');

您可以使用字符串或数组来指定正在从中选择的表。表名可能包含模式前缀和/或表别名，就像您在编写原始 SQL 语句时一样。例如：

$query->from(['public.user u', 'public.post p']);

// equivalent to:

$query->from('public.user u, public.post p');

如果您使用数组格式，您也可以使用数组键来指定表别名，如下所示

$query->from(['u' => 'public.user', 'p' => 'public.post']);

除了表名之外，您还可以通过使用 yii\db\Query 对象来指定它们，从子查询中选择。例如：

$subQuery = (new Query())->select('id')->from('user')->where('status=1');

// SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u 
$query->from(['u' => $subQuery]);

前缀 ¶

还可以应用默认的 tablePrefix。实现说明位于 "数据库访问对象"指南的"引用表"部分。

orderBy() ¶

orderBy() 方法指定 SQL 查询的 ORDER BY 部分。例如，

// ... ORDER BY `id` ASC, `name` DESC
$query->orderBy([
    'id' => SORT_ASC,
    'name' => SORT_DESC,
]);

在上面的代码中，数组键是列名，而数组值是相应的排序方向。PHP 常量 SORT_ASC 指定升序排序，而 SORT_DESC 指定降序排序。

如果 ORDER BY 仅涉及简单的列名，您可以使用字符串指定它，就像您在编写原始 SQL 语句时一样。例如，

$query->orderBy('id ASC, name DESC');

注意：如果 ORDER BY 涉及一些数据库表达式，则应使用数组格式。

您可以调用 addOrderBy() 将其他列添加到 ORDER BY 部分。例如，

$query->orderBy('id ASC')
    ->addOrderBy('name DESC');

groupBy() ¶

groupBy() 方法指定 SQL 查询的 GROUP BY 部分。例如，

// ... GROUP BY `id`, `status`
$query->groupBy(['id', 'status']);

如果 GROUP BY 仅涉及简单的列名，您可以使用字符串指定它，就像您在编写原始 SQL 语句时一样。例如，

$query->groupBy('id, status');

注意：如果 GROUP BY 涉及一些数据库表达式，则应使用数组格式。

您可以调用 addGroupBy() 将其他列添加到 GROUP BY 部分。例如，

$query->groupBy(['id', 'status'])
    ->addGroupBy('age');

having() ¶

having() 方法指定 SQL 查询的 HAVING 部分。它接受一个条件，该条件可以与 where() 的条件相同方式指定。例如，

// ... HAVING `status` = 1
$query->having(['status' => 1]);

有关如何指定条件的更多详细信息，请参阅 where() 的文档。

您可以调用 andHaving() 或 orHaving() 将其他条件追加到 HAVING 部分。例如，

// ... HAVING (`status` = 1) AND (`age` > 30)
$query->having(['status' => 1])
    ->andHaving(['>', 'age', 30]);

limit() 和 offset() ¶

limit() 和 offset() 方法指定 SQL 查询的 LIMIT 和 OFFSET 部分。例如，

// ... LIMIT 10 OFFSET 20
$query->limit(10)->offset(20);

如果您指定了无效的限制或偏移量（例如负值），则它将被忽略。

信息：对于不支持 LIMIT 和 OFFSET 的 DBMS（例如 MSSQL），查询构建器将生成一个模拟 LIMIT/OFFSET 行为的 SQL 语句。

withQuery() ¶

withQuery() 方法指定 SQL 查询的 WITH 前缀。您可以使用它来代替子查询，以提高可读性和一些独特的功能（递归 CTE）。在 modern-sql 中了解更多信息。例如，此查询将递归地选择 admin 的所有嵌套权限及其子权限，

$initialQuery = (new \yii\db\Query())
    ->select(['parent', 'child'])
    ->from(['aic' => 'auth_item_child'])
    ->where(['parent' => 'admin']);

$recursiveQuery = (new \yii\db\Query())
    ->select(['aic.parent', 'aic.child'])
    ->from(['aic' => 'auth_item_child'])
    ->innerJoin('t1', 't1.child = aic.parent');

$mainQuery = (new \yii\db\Query())
    ->select(['parent', 'child'])
    ->from('t1')
    ->withQuery($initialQuery->union($recursiveQuery), 't1', true);

withQuery() 可以多次调用，以将更多 CTE 附加到主查询之前。查询将按照它们附加的顺序附加。如果其中一个查询是递归的，则整个 CTE 变成递归的。

查询方法 ¶

yii\db\Query 提供了一整套用于不同查询目的的方法

all()：返回一个行数组，其中每行都是一个名称-值对的关联数组。
one()：返回结果的第一行。
column()：返回结果的第一列。
scalar()：返回位于结果第一行第一列的标量值。
exists()：返回一个值，指示查询是否包含任何结果。
count()：返回 COUNT 查询的结果。
其他聚合查询方法，包括 sum($q)、average($q)、max($q)、min($q)。$q 参数对于这些方法是必需的，可以是列名或数据库表达式。

例如，

// SELECT `id`, `email` FROM `user`
$rows = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->all();
    
// SELECT * FROM `user` WHERE `username` LIKE `%test%`
$row = (new \yii\db\Query())
    ->from('user')
    ->where(['like', 'username', 'test'])
    ->one();

注意：one() 方法只返回查询结果的第一行。它不会在生成的 SQL 语句中添加 LIMIT 1。如果您知道查询只返回一行或几行数据（例如，如果您使用某些主键进行查询），这很好，而且是首选。但是，如果查询可能会产生大量数据，则应该显式调用 limit(1) 以提高性能，例如 (new \yii\db\Query())->from('user')->limit(1)->one()。

所有这些查询方法都接受一个可选的 $db 参数，它代表应该用于执行数据库查询的数据库连接。如果您省略此参数，则将使用 db 应用程序组件作为数据库连接。下面是另一个使用 count() 查询方法的示例

// executes SQL: SELECT COUNT(*) FROM `user` WHERE `last_name`=:last_name
$count = (new \yii\db\Query())
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->count();

当您调用 yii\db\Query 的查询方法时，它实际上在内部执行以下工作

调用 yii\db\QueryBuilder 以根据 yii\db\Query 的当前构造生成 SQL 语句；
使用生成的 SQL 语句创建 yii\db\Command 对象；
调用 yii\db\Command 的查询方法（例如 queryAll()）以执行 SQL 语句并检索数据。

有时，您可能想要检查或使用从 yii\db\Query 对象构建的 SQL 语句。您可以使用以下代码来实现此目标

$command = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->limit(10)
    ->createCommand();
    
// show the SQL statement
echo $command->sql;
// show the parameters to be bound
print_r($command->params);

// returns all rows of the query result
$rows = $command->queryAll();

索引查询结果 ¶

当您调用 all() 时，它将返回一个行数组，这些行由连续的整数索引。有时您可能希望以不同的方式索引它们，例如按特定列或表达式值索引。您可以通过在 all() 之前调用 indexBy() 来实现此目标。例如，

// returns [100 => ['id' => 100, 'username' => '...', ...], 101 => [...], 103 => [...], ...]
$query = (new \yii\db\Query())
    ->from('user')
    ->limit(10)
    ->indexBy('id')
    ->all();

传递到 indexBy() 方法中的列名必须存在于结果集中，才能使索引工作 - 由开发人员负责确保这一点。

要按表达式值索引，请将匿名函数传递给 indexBy() 方法

$query = (new \yii\db\Query())
    ->from('user')
    ->indexBy(function ($row) {
        return $row['id'] . $row['username'];
    })->all();

匿名函数接受一个参数 $row，它包含当前行数据，并应返回一个标量值，该值将用作当前行的索引值。

注意：与 groupBy() 或 orderBy() 等查询方法（它们被转换为 SQL 并成为查询的一部分）相比，此方法在从数据库中获取数据后运行。这意味着只能使用查询中 SELECT 的那些列名。另外，如果您选择了带表前缀的列，例如 customer.id，则结果集将只包含 id，因此您必须调用 ->indexBy('id') 而不带表前缀。

批量查询 ¶

在处理大量数据时，诸如 yii\db\Query::all() 等方法不适合，因为它们需要将整个查询结果加载到客户端的内存中。为了解决此问题，Yii 提供了批量查询支持。服务器保存查询结果，而客户端使用游标一次一批地迭代结果集。

警告：MySQL 实现的批量查询存在已知的限制和解决方法。见下文。

批量查询可以使用如下方式

use yii\db\Query;

$query = (new Query())
    ->from('user')
    ->orderBy('id');

foreach ($query->batch() as $users) {
    // $users is an array of 100 or fewer rows from the user table
}

// or to iterate the row one by one
foreach ($query->each() as $user) {
    // data is being fetched from the server in batches of 100,
    // but $user represents one row of data from the user table
}

方法 yii\db\Query::batch() 和 yii\db\Query::each() 返回一个 yii\db\BatchQueryResult 对象，该对象实现了 Iterator 接口，因此可以在 foreach 结构中使用。在第一次迭代时，会向数据库发出一个 SQL 查询。然后，在剩余的迭代中，数据将分批获取。默认情况下，批次大小为 100，这意味着每次批次获取 100 行数据。可以通过将第一个参数传递给 batch() 或 each() 方法来更改批次大小。

与 yii\db\Query::all() 相比，批次查询一次只将 100 行数据加载到内存中。

如果通过 yii\db\Query::indexBy() 指定查询结果以某列为索引，则批次查询仍将保持正确的索引。

例如

$query = (new \yii\db\Query())
    ->from('user')
    ->indexBy('username');

foreach ($query->batch() as $users) {
    // $users is indexed by the "username" column
}

foreach ($query->each() as $username => $user) {
    // ...
}

MySQL 中批次查询的局限性 ¶

MySQL 中的批次查询实现依赖于 PDO 驱动程序库。默认情况下，MySQL 查询是 buffered。这违背了使用游标获取数据的目的，因为它不能阻止驱动程序将整个结果集加载到客户端的内存中。

注意： 当使用 libmysqlclient（PHP5 的典型情况）时，PHP 的内存限制不会计算用于结果集的内存。看起来批次查询工作正常，但实际上整个数据集已加载到客户端的内存中，并且有可能耗尽内存。

要禁用缓冲并减少客户端内存需求，必须将 PDO 连接属性 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 设置为 false。但是，在检索整个数据集之前，无法通过相同的连接进行其他查询。这可能会阻止 ActiveRecord 发出查询以获取它需要时的表模式。如果这不是问题（表模式已缓存），则可以将原始连接切换到非缓冲模式，然后在批次查询完成后回滚。

Yii::$app->db->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

// Do batch query

Yii::$app->db->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

注意： 在 MyISAM 的情况下，在批次查询期间，表可能会被锁定，从而延迟或拒绝其他连接的写入访问。使用非缓冲查询时，尽量使游标打开时间最短。

如果模式未被缓存，或者需要在处理批次查询时运行其他查询，则可以创建另一个非缓冲连接到数据库

$unbufferedDb = new \yii\db\Connection([
    'dsn' => Yii::$app->db->dsn,
    'username' => Yii::$app->db->username,
    'password' => Yii::$app->db->password,
    'charset' => Yii::$app->db->charset,
]);
$unbufferedDb->open();
$unbufferedDb->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

如果要确保 $unbufferedDb 与原始缓冲 $db 具有完全相同的 PDO 属性，但 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 为 false，请考虑对 $db 进行深复制，然后手动将其设置为 false。

然后，查询将正常创建。新连接用于运行批次查询并以批次或逐个的方式检索结果

// getting data in batches of 1000
foreach ($query->batch(1000, $unbufferedDb) as $users) {
    // ...
}


// data is fetched from server in batches of 1000, but is iterated one by one 
foreach ($query->each(1000, $unbufferedDb) as $user) {
    // ...
}

当不再需要连接并且已检索到结果集时，可以关闭连接

$unbufferedDb->close();

注意： 非缓冲查询在 PHP 端使用的内存更少，但可能会增加 MySQL 服务器的负载。建议您根据自己的生产实践设计自己的代码以处理超大量数据，例如，将整数键的范围划分，并使用非缓冲查询循环它们。

添加自定义条件和表达式 ¶

如条件 - 对象格式一章所述，可以创建自定义条件类。例如，让我们创建一个条件来检查特定列是否小于某个值。使用运算符格式，它看起来像这样

[
    'and',
    ['>', 'posts', $minLimit],
    ['>', 'comments', $minLimit],
    ['>', 'reactions', $minLimit],
    ['>', 'subscriptions', $minLimit]
]

当此类条件应用一次时，就可以了。如果在单个查询中多次使用它，可以对其进行大量优化。让我们创建一个自定义条件对象来演示它。

Yii 有一个 ConditionInterface，它必须用于标记表示条件的类。它需要 fromArrayDefinition() 方法实现，以便可以从数组格式创建条件。如果您不需要它，可以实现此方法并抛出异常。

由于我们创建了自己的自定义条件类，因此我们可以构建最适合我们任务的 API。

namespace app\db\conditions;

class AllGreaterCondition implements \yii\db\conditions\ConditionInterface
{
    private $columns;
    private $value;

    /**
     * @param string[] $columns Array of columns that must be greater, than $value
     * @param mixed $value the value to compare each $column against.
     */
    public function __construct(array $columns, $value)
    {
        $this->columns = $columns;
        $this->value = $value;
    }
    
    public static function fromArrayDefinition($operator, $operands)
    {
        throw new InvalidArgumentException('Not implemented yet, but we will do it later');
    }
    
    public function getColumns() { return $this->columns; }
    public function getValue() { return $this->vaule; }
}

因此，我们可以创建一个条件对象

$condition = new AllGreaterCondition(['col1', 'col2'], 42);

但是 QueryBuilder 仍然不知道如何从该对象生成 SQL 条件。现在我们需要为该条件创建一个生成器。它必须实现 yii\db\ExpressionBuilderInterface，这要求我们实现 build() 方法。

namespace app\db\conditions;

class AllGreaterConditionBuilder implements \yii\db\ExpressionBuilderInterface
{
    use \yii\db\ExpressionBuilderTrait; // Contains constructor and `queryBuilder` property.

    /**
     * @param ExpressionInterface $condition the condition to be built
     * @param array $params the binding parameters.
     * @return AllGreaterCondition
     */ 
    public function build(ExpressionInterface $expression, array &$params = [])
    {
        $value = $condition->getValue();
        
        $conditions = [];
        foreach ($expression->getColumns() as $column) {
            $conditions[] = new SimpleCondition($column, '>', $value);
        }

        return $this->queryBuilder->buildCondition(new AndCondition($conditions), $params);
    }
}

然后简单地让 QueryBuilder 了解我们的新条件 - 在 expressionBuilders 数组中为它添加一个映射。这可以在应用程序配置中直接完成

'db' => [
    'class' => 'yii\db\mysql\Connection',
    // ...
    'queryBuilder' => [
        'expressionBuilders' => [
            'app\db\conditions\AllGreaterCondition' => 'app\db\conditions\AllGreaterConditionBuilder',
        ],
    ],
],

现在我们可以在 where() 中使用我们的条件

$query->andWhere(new AllGreaterCondition(['posts', 'comments', 'reactions', 'subscriptions'], $minValue));

如果我们想使用运算符格式创建自定义条件，我们应该在 QueryBuilder::conditionClasses 中声明它

'db' => [
    'class' => 'yii\db\mysql\Connection',
    // ...
    'queryBuilder' => [
        'expressionBuilders' => [
            'app\db\conditions\AllGreaterCondition' => 'app\db\conditions\AllGreaterConditionBuilder',
        ],
        'conditionClasses' => [
            'ALL>' => 'app\db\conditions\AllGreaterCondition',
        ],
    ],
],

并在 app\db\conditions\AllGreaterCondition 中创建 AllGreaterCondition::fromArrayDefinition() 方法的实际实现

namespace app\db\conditions;

class AllGreaterCondition implements \yii\db\conditions\ConditionInterface
{
    // ... see the implementation above
     
    public static function fromArrayDefinition($operator, $operands)
    {
        return new static($operands[0], $operands[1]);
    }
}

之后，我们可以使用更短的运算符格式创建自定义条件

$query->andWhere(['ALL>', ['posts', 'comments', 'reactions', 'subscriptions'], $minValue]);

您可能会注意到，这里使用了两个概念：表达式和条件。有一个 yii\db\ExpressionInterface 应该用于标记需要表达式生成器类的对象，该生成器类实现 yii\db\ExpressionBuilderInterface 来进行构建。此外，还有一个 yii\db\condition\ConditionInterface，它扩展了 ExpressionInterface 并且应该用于可以从数组定义创建的对象，如上所示，但还需要生成器。

总结一下

表达式 - 是数据集的数据传输对象 (DTO)，可以以某种方式编译成某个 SQL 语句（运算符、字符串、数组、JSON 等）。
条件 - 是表达式的超集，它聚合了可以编译成单个 SQL 条件的多个表达式（或标量值）。

您可以创建自己的实现 ExpressionInterface 的类，以隐藏将数据转换为 SQL 语句的复杂性。您将在下一篇文章中了解有关其他表达式示例的更多信息；

数据库访问对象

返回顶部

活动记录

发现错字或您认为此页面需要改进？
在 github 上编辑它 !

查询构建器 ¶

构建查询 ¶

select() ¶

from() ¶

前缀 ¶

where() ¶

字符串格式 ¶

哈希格式 ¶

运算符格式 ¶

对象格式 ¶

追加条件 ¶

过滤条件 ¶