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

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

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

$rows = (new yiidbQuery())

    ->select("id, name")

    ->from("user")

    ->limit(10)

    ->all();

 

// 与下面代码等值

 

$query = (new yiidbQuery())

    ->select("id, name")

    ->from("user")

    ->limit(10);

 

// 创建一个command

$command = $query->createCommand();

 

// 执行一个command

$rows = $command->queryAll();

 

Query Methods

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

 

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

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

[[yiidbQuery::one()|one()]]:返回结果集数组的第一行。

[[yiidbQuery::column()|column()]]:返回结果集的第一列。

[[yiidbQuery::scalar()|scalar()]]: 返回结果集的第一行第一列。

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

[[yiidbQuery::count()|count()]]:返回查询结果的数量。

 

Building Query

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

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);