yii2数据库查询生成器--排序聚合函数orderBy、groupBy-yii2数据库使用-编程文章分享

orderBy()

yii\db\Query::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');

调用 [yii\db\Query::addOrderBy()|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');

调用 [yii\db\Query::addOrderBy()|addOrderBy()]] 为 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 (`id` > 30)
$query->having(['status' => 1])
      ->andHaving(['>', 'id', 30]);

yii\db\Query::limit() 和 yii\db\Query::offset()

yii\db\Query::limit() 和 yii\db\Query::offset() 是用来指定 SQL 语句当中的 LIMIT 和 OFFSET 子句。如，

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

如果指定了一个无效的 limit 或者 offset（例如，一个负数或null），那么它将会被忽略掉。

查询方法

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

count(): 返回 COUNT 查询的结果。

其它集合查询方法: 包括 sum($q), average($q), max($q), min($q) 等. $q 是一个必选参数，既可以是一个字段名称，又可以是一个 DB 表达式。如：

// 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() 方法只返回查询结果当中的第一条数据，条件语句中不会加上 LIMIT 1 条件。如果你清楚的知道查询将会只返回一行或几行数据（例如，如果你是通过某些主键来查询的），这很好也提倡这样做。但是，如果查询结果有机会返回大量的数据时，那么你应该显示调用 limit(1) 方法，以改善性能。例如， (new \yii\db\Query())->from('user')->limit(1)->one()。

所有的这些查询方法都有一个可选的参数 $db, 该参数指代的是 DB connection，执行一个 DB 查询时会用到。如果你省略了这个参数，那么 db application component 将会被用作默认的 DB 连接。如下是另外一个使用 count() 查询的例子：

// 执行 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\Query 的构造基础之上，调用 yii\db\QueryBuilder 来生成一条 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();

// 打印 SQL 语句
echo $command->sql;
// 打印被绑定的参数
print_r($command->params);

// 返回查询结果的所有行
$rows = $command->queryAll();

索引查询结果

当你在调用 all() 方法时，它将返回一个以连续的整型数值为索引的数组。而有时候你可能希望使用一个特定的字段或者表达式的值来作为索引结果集数组。那么你可以在调用 all() 之前使用 yii\db\Query::indexBy() 方法来达到这个目的。例如，

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

如需使用表达式的值做为索引，那么只需要传递一个匿名函数给 yii\db\Query::indexBy() 方法即可：

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

该匿名函数将带有一个包含了当前行的数据的 $row 参数，并且返回用作当前行索引的标量值（译者注：就是简单的数值或者字符串，而不是其他复杂结构，例如数组）。

groupBy() 或 yii\db\Query::orderBy() 方法会生成sql对应的部分,indexBy()方法对sql查询的结果进行处理，也就是只有select后的字段名才能参与索引。

批处理查询

当需要处理大数据的时候，像 yii\db\Query::all() 这样的方法就不太合适了，因为它们会把所有数据都读取到内存上。为了保持较低的内存需求， Yii 提供了一个所谓的批处理查询的支持。批处理查询会利用数据游标将数据以批为单位取出来。

批处理查询的用法如下：

use yii\db\Query;

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

foreach ($query->batch() as $users) {
    // $users 是一个包含100条或小于100条用户表数据的数组
}

// or if you want to iterate the row one by one
foreach ($query->each() as $user) {
    // $user 指代的是用户表当中的其中一行数据
}

yii\db\Query::batch() 和 yii\db\Query::each() 方法将会返回一个实现了Iterator 接口 yii\db\BatchQueryResult 的对象，可以用在 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 的 “username” 字段将会成为索引
}

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

yii2数据库查询生成器--排序聚合函数orderBy、groupBy

相关推荐

请登录