Yii - Query Builder and Query(查询生成器)

Query Builder and Query

Yii提供一个底层方式去和数据库交互。在不使用Yii Query Builder情况下，这会成为一件比较繁琐和容易出错的事。一个可选的方法就是使用Query Builder。它是一个面向对象的工具，可以生成多种可执行的查询。

以下是一个典型的query builder使用:

$rows = (new \yii\db\Query())

    ->select('id, name')

    ->from('user')

    ->limit(10)

    ->all();

 

// 与下面代码等值

 

$query = (new \yii\db\Query())

    ->select('id, name')

    ->from('user')

    ->limit(10);

 

// 创建一个command

$command = $query->createCommand();

 

// 执行一个command

$rows = $command->queryAll();

 

Query Methods

正如你看到的， [[yii\db\Query]]是你一直都使用的一个方法。在这之前，Query只是负责表现各种查询信息。而事实上,当你调用createCommand()方式时,query building 的逻辑是 由[[yii\db\QueryBuilder]] 完成的，而查询执行是由[[yii\db\Command]]完成的。

 

为了方便，[[yii\db\Query]]提供一个公用的查询方法集合，并且能返回结果，例如：

[[yii\db\Query::all()|all()]]:建立这个查询，执行它，并返回一个结果集数组。

[[yii\db\Query::one()|one()]]:返回结果集数组的第一行。

[[yii\db\Query::column()|column()]]:返回结果集的第一列。

[[yii\db\Query::scalar()|scalar()]]: 返回结果集的第一行第一列。

[[yii\db\Query::exists()|exists()]]: 返回一个值表明查询结果是否存在。

[[yii\db\Query::count()|count()]]:返回查询结果的数量。

 

Building Query

接下来，我们会解析怎么去生成各种查询子句。简单地，我们使用$query去引用一个[[yii\db\Query]]对象。

SELECT:

为了生成一个基础的select查询，你需要去指出你要查询哪一列和来自哪一个表。

$query->select('id, name')->from('user');

select选项可以将命令字符串划分来指定。当动态形成查询时，数组方法是最高效的。

$query->select(['id', 'name'])->from('user');

 

注意，如果使用select查询子句的时候，你应该全部使用数组格式。当指定一个列的时候，你应该函括表的前缀或者列的别名。如果你用一个数组去指定列，你应该使用数组键去指定列的别名。譬如：'user_id' => 'user.id', 'user_name' => 'user.name']

 

为了选择已经区分了的行，你可以使用distinct(),如：

$query->select('user_id')->distinct()->from('post');

 

FROM

为了指定出你要筛选数据的表，你需要用from();

$query->select('*')->from('user');

 

有时，你需要用到多表查询。而表名可以包含模式前缀如public.user或者别名user u。这个方法会自动引用这个表名，除非你的表名包含附加语（即你的表用于子查询或者DB表达）

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

 

而当多个表被指定了各自的别名后，可用数组键值作为别名，如：

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

 

当然，你也可以用query对象作子查询。在这，响应的数组键会被作为别名用于子查询。

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

$query->select('*')->from(['u' => $subQuery]);

 

WHERE

不少时候，筛选数据总有有其条件。QueryBuilder有些高效的筛选方法，where可以有以下几种方式。

 

最简单方式：

$query->where('status=:status', [':status' => $status]);

 

不建议下面的方式：

$query->where("status=$status"); // Dangerous!

 

如果需要用到参数，你可以用以下方式：

$query->where('status=:status');

$query->addParams([':status' => $status]);

 

多条件可以这样：

$query->where([

    'status' => 10,

    'type' => 2,

    'id' => [4, 8, 15, 16, 23, 42],

]);

代码会生成这样的SQL：

WHERE (`status` = 10) AND (`type` = 2) AND (`id` IN (4, 8, 15, 16, 23, 42))

 

当然，Query Builder也可以处理空值，例如：

$query->where(['status' => null]);

生成的部分SQL如下：

WHERE (`status` IS NULL)

 

子查询可以这样：

$userQuery = (new Query)->select('id')->from('user');

$query->where(['id' => $userQuery]);

生成的SQL如下：

WHERE `id` IN (SELECT `id` FROM `user`)

 

如果你需要用到其他的操作符，你需要用这种格式[操作符, 操作数1, 操作数2, ...].

操作符有如下：

and: ['and', 'id=1', 'id=2']将会生成id=1 AND id=2.

       ['and', 'type=1', ['or', 'id=1', 'id=2']]将会生成type=1 AND (id=1 OR id=2). 

or: 用法与and相似。

between:['between', 'id', 1, 10]将会生成id BETWEEN 1 AND 10.

in: ['in', 'id', [1, 2, 3]]将会生成id IN (1, 2, 3).

like: ['like', 'name', 'tester']将会生成name LIKE '%tester%',

     ['like', 'name', ['test', 'sample']] 将会生成name LIKE '%test%' AND name LIKE '%sample%'.

当然，你也可以用操作符：

$userQuery = (new Query)->select('id')->from('user');

$query->where(['>=', 'id', 10]);

生成的SQL：

SELECT id FROM user WHERE id >= 10;

Building Filter Conditions（过滤条件）

filterWhere()方法：

// $username and $email 来自表格输入

$query->filterWhere([

    'username' => $username,

    'email' => $email,

]);

filterWhere()这个方法与where()非常相似。它们之间主要不同的地方是前者会移除空值。如果$email是空值，查询结果将会是 ...WHERE username=:username;如果$username和$email都为空，where语句将会移除。

 

ORDER BY

orderBy和addOrderBy可以这样用：

$query->orderBy([

    'id' => SORT_ASC,

    'name' => SORT_DESC,

]);

在这里，排序方式会以id为升序name为降序排列。

 

GROUP BY and HAVING

你可以这样使用group by：

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

同样，having也是如此，它和where类似：

$query->having(['status' => $status]);

LIMIT and OFFSET

如果你希望限制你的结果在10行内，你可以：

$query->limit(10);

如果你希望跳至第100行，你可以：

$query->offset(100);

 

JOIN

join子句可以用QueryBuilder的如下方法生成：

1. innerJoin()
2. leftJoin()
3. rightJoin()

以下的这个左联结通过一个查询从连个相关联的表中筛选出数据：

 

$query->select(['user.name AS author', 'post.title as title'])

    ->from('user')

    ->leftJoin('post', 'post.user_id = user.id');

从上面的代码看出，左联结方法的第一个参数是用于指定的联结表。第二个参数是联结条件。

 

UNION

union操作符在SQL里面可以将一个查询的结果合并到另一个查询结果里面。查询将会返回合并后的列。在Yii,为了达到这母的，你可以使用union方法：

$query = new Query();

$query->select("id, category_id as type, name")->from('post')->limit(10);

 

$anotherQuery = new Query();

$anotherQuery->select('id, type, name')->from('user')->limit(10);

 

$query->union($anotherQuery);