入门5查询生成器

2023-12-30 23:40:58

查询构建器

查询构建器建立在 Database Access Objects 基础之上，可让你创建程序化的、DBMS无关的SQL语句。相比于原生的SQL语句，查询构建器可以帮你写出可读性更强的SQL相关的代码，并生成安全性更强的SQL语句。

使用查询构建器通常包含以下两个步骤：

创建一个 yii\db\Query 对象来代表一条 SELECT SQL 语句的不同子句（例如 SELECT, FROM）。
执行 yii\db\Query 的一个查询方法（例如：all()）从数据库当中检索数据。

创建查询

为了创建一个 yii\db\Query 对象，你需要调用不同的查询构建方法来代表SQL语句的不同子句。这些方法的名称集成了在SQL语句相应子句中使用的关键字。例如，为了指定 SQL 语句当中的 FROM 子句，你应该调用 from() 方法。所有的查询构建器方法返回的是查询对象本身，也就是说，你可以把多个方法的调用串联起来。

select()

如果你在组建查询时没有调用 select() 方法，那么选择的将是 '*' ，也即选取的是所有的字段。

除了字段名称以外，你还可以选择数据库的表达式。当你使用到包含逗号的数据库表达式的时候，你必须使用数组的格式，以避免自动的错误的引号添加。例如：

从 2.0.1 的版本开始你就可以使用子查询了。在定义每一个子查询的时候，你应该使用 yii\db\Query 对象。例如：

你可以调用 addSelect() 方法来选取附加字段，例如：

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

from()

from() 方法指定了 SQL 语句当中的 FROM 子句。例如：

除了表名以外，你还可以从子查询中再次查询，这里的子查询是由 yii\db\Query 创建的对象。例如：

where()

where() 方法定义了 SQL 语句当中的 WHERE 子句。你可以使用如下四种格式来定义 WHERE 条件：

字符串格式，例如：'status=1'
哈希格式，例如： ['status' => 1, 'type' => 2]
操作符格式，例如：['like', 'name', 'test']
对象格式，例如：new LikeCondition('name', 'LIKE', 'test')

有时候，你也许想要测试或者使用一个由 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() 之前使用 indexBy() 方法来达到这个目的。例如，

批处理查询

当需要处理大数据的时候，像 yii\db\Query::all() 这样的方法就不太合适了，因为它们会把所有查询的数据都读取到客户端内存上。为了解决这个问题， Yii 提供了批处理查询的支持。服务端先保存查询结果，然后客户端使用游标（cursor）每次迭代出固定的一批结果集回来。

活动记录

Active Record 提供了一个面向对象的接口，用以访问和操作数据库中的数据。Active Record 类与数据库表关联， Active Record 实例对应于该表的一行， Active Record 实例的属性表示该行中特定列的值。您可以访问 Active Record 属性并调用 Active Record 方法来访问和操作存储在数据库表中的数据，而不用编写原始 SQL 语句。

声明 Active Record 类

要想声明一个 Active Record 类，你需要声明该类继承 yii\db\ActiveRecord。

设置表的名称

默认的，每个 Active Record 类关联各自的数据库表。经过 yii\helpers\Inflector::camel2id() 处理，tableName() 方法默认返回的表名称是通过类名转换来得。如果这个默认名称不正确，你得重写这个方法。

将 Active Record 称为模型

Active Record 实例称为模型。因此, 我们通常将 Active Record 类放在 app\models 命名空间下（或者其他保存模型的命名空间）。

因为 yii\db\ActiveRecord 继承了模型 yii\base\Model，它就拥有所有模型特性，比如说属性（attributes），验证规则（rules），数据序列化（data serialization），等等。

查询数据

定义 Active Record 类后，你可以从相应的数据库表中查询数据。查询过程大致如下三个步骤：

通过 yii\db\ActiveRecord::find() 方法创建一个新的查询生成器对象；
使用查询生成器的构建方法来构建你的查询；
调用查询生成器的查询方法来取出数据到 Active Record 实例中。

正如你看到的，是不是跟查询生成器的步骤差不多。唯一有区别的地方在于你用 yii\db\ActiveRecord::find() 去获得一个新的查询生成器对象，这个对象是 yii\db\ActiveQuery，而不是使用 new 操作符创建一个查询生成器对象。

访问数据

如上所述，从数据库返回的数据被填充到 Active Record 实例中，查询结果的每一行对应于单个 Active Record 实例。您可以通过 Active Record 实例的属性来访问列值，例如，

数据转换

常常遇到，要输入或显示的数据是一种格式，而要将其存储在数据库中是另一种格式。例如，在数据库中，您将客户的生日存储为 UNIX 时间戳（虽然这不是一个很好的设计），而在大多数情况下，你想以字符串 'YYYY/MM/DD' 的格式处理生日数据。为了实现这一目标，您可以在 Customer 中定义 数据转换 方法定义 Active Record 类如下

以数组形式获取数据

通过 Active Record 对象获取数据十分方便灵活，与此同时，当你需要返回大量的数据的时候，这样的做法并不令人满意，因为这将导致大量内存占用。在这种情况下，您可以在查询方法前调用 asArray() 方法，来获取 PHP 数组形式的结果：

批量获取数据

在查询生成器中，我们已经解释说可以使用 批处理查询 来最小化你的内存使用，每当从数据库查询大量数据。你可以在 Active Record 中使用同样的技巧。例如，

保存数据

使用 Active Record，您可以通过以下步骤轻松地将数据保存到数据库：

准备一个 Active Record 实例
将新值赋给 Active Record 的属性
调用 yii\db\ActiveRecord::save() 保存数据到数据库中。

例如，

数据验证

因为 yii\db\ActiveRecord 继承于 yii\base\Model，它共享相同的输入验证功能。你可以通过重写 rules() 方法声明验证规则并执行，通过调用 validate() 方法进行数据验证。

当你调用 save() 时，默认情况下会自动调用 validate()。只有当验证通过时，它才会真正地保存数据; 否则将简单地返回 false，您可以检查 errors 属性来获取验证过程的错误消息。

块赋值

和普通的模型一样，你亦可以享受 Active Record 实例的块赋值特性。使用此功能，您可以在单个 PHP 语句中，给 Active Record 实例的多个属性批量赋值，如下所示。记住，只有安全属性才可以批量赋值。

更新多个数据行

上述方法都可以用于单个 Active Record 实例，以插入或更新单条表数据行。要同时更新多个数据行，你应该调用 updateAll() 这是一个静态方法。

乐观锁

乐观锁是一种防止此冲突的方法：一行数据同时被多个用户更新。例如，同一时间内，用户 A 和用户 B 都在编辑相同的 wiki 文章。用户 A 保存他的编辑后，用户 B 也点击“保存”按钮来保存他的编辑。实际上，用户 B 正在处理的是过时版本的文章，因此最好是，想办法阻止他保存文章并向他提示一些信息。

乐观锁通过使用一个字段来记录每行的版本号来解决上述问题。当使用过时的版本号保存一行数据时，yii\db\StaleObjectException 异常将被抛出，这阻止了该行的保存。乐观锁只支持更新 yii\db\ActiveRecord::update() 或者删除 yii\db\ActiveRecord::delete() 已经存在的单条数据行。

使用乐观锁的步骤，

在与 Active Record 类相关联的 DB 表中创建一个列，以存储每行的版本号。这个列应当是长整型（在 MySQL 中是 BIGINT DEFAULT 0）。
重写 yii\db\ActiveRecord::optimisticLock() 方法返回这个列的命名。
在你的 Model 类里实现 OptimisticLockBehavior 行为（注：这个行为类在 2.0.16 版本加入），以便从请求参数里自动解析这个列的值。然后从验证规则中删除 version 属性，因为 OptimisticLockBehavior 已经处理它了.
在用于用户填写的 Web 表单中，添加一个隐藏字段（hidden field）来存储正在更新的行的当前版本号。
在使用 Active Record 更新数据的控制器动作中，要捕获（try/catch） yii\db\StaleObjectException 异常。实现一些业务逻辑来解决冲突（例如合并更改，提示陈旧的数据等等）。

选择额外的字段

当 Active Record 实例从查询结果中填充时，从数据结果集中，其属性的值将被相应的列填充。

你可以从查询中获取其他列或值，并将其存储在 Active Record 活动记录中。例如，假设我们有一个名为 room 的表，其中包含有关酒店可用房间的信息。每个房间使用字段 length，width，height 存储有关其空间大小的信息。想象一下，我们需要检索出所有可用房间的列表，并按照体积大小倒序排列。你不可能使用 PHP 来计算体积，但是，由于我们需要按照它的值对这些记录进行排序，你依然需要 volume （体积）来显示在这个列表中。为了达到这个目标，你需要在你的 Room 活动记录类中声明一个额外的字段，它将存储 volume 的值：

然后，你需要撰写一个查询，它可以计算房间的大小并执行排序：

使用关联数据

除了处理单个数据库表之外，Active Record 还可以将相关数据集中进来，使其可以通过原始数据轻松访问。例如，客户数据与订单数据相关因为一个客户可能已经存放了一个或多个订单。这种关系通过适当的声明，你可以使用 $customer->orders 表达式访问客户的订单信息这表达式将返回包含 Order Active Record 实例的客户订单信息的数组。

声明关联关系

你必须先在 Active Record 类中定义关联关系，才能使用 Active Record 的关联数据。简单地为每个需要定义关联关系声明一个 关联方法 即可，如下所示，

上述的代码中，我们为 Customer 类声明了一个 orders 关联，和为 Order 声明了一个 customer 关联。

每个关联方法必须这样命名：getXyz。然后我们通过 xyz（首字母小写）调用这个关联名。请注意关联名是大小写敏感的。

当声明一个关联关系的时候，必须指定好以下的信息：

关联的对应关系：通过调用 hasMany() 或者 hasOne() 指定。在上面的例子中，您可以很容易看出这样的关联声明：一个客户可以有很多订单，而每个订单只有一个客户。
相关联 Active Record 类名：用来指定为 hasMany() 或者 hasOne() 方法的第一个参数。推荐的做法是调用 Xyz::className() 来获取类名称的字符串，以便您可以使用 IDE 的自动补全，以及让编译阶段的错误检测生效。

两组数据的关联列：用以指定两组数据相关的列（hasOne()/hasMany() 的第二个参数）。数组的值填的是主数据的列（当前要声明关联的 Active Record 类为主数据），而数组的键要填的是相关数据的列。

一个简单的口诀，先附表的主键，后主表的主键。正如上面的例子，customer_id 是 Order 的属性，而 id是 Customer 的属性。（译者注：hasMany() 的第二个参数，这个数组键值顺序不要弄反了）

访问关联数据

定义了关联关系后，你就可以通过关联名访问相应的关联数据了。就像访问一个由关联方法定义的对象一样，具体概念请查看属性。因此，现在我们可以称它为 关联属性 了。

如果使用 hasMany() 声明关联关系，则访问此关联属性将返回相关的 Active Record 实例的数组；如果使用 hasOne() 声明关联关系，访问此关联属性将返回相关的 Active Record 实例，如果没有找到相关数据的话，则返回 null。

当你第一次访问关联属性时，将执行 SQL 语句获取数据，如上面的例子所示。如果再次访问相同的属性，将返回先前的结果，而不会重新执行 SQL 语句。要强制重新执行 SQL 语句，你应该先 unset 这个关联属性，如：unset（$ customer-> orders）。

动态关联查询

由于关联方法返回 yii\db\ActiveQuery 的实例，因此你可以在执行 DB 查询之前，使用查询构建方法进一步构建此查询。例如，

与访问关联属性不同，每次通过关联方法执行动态关联查询时，都会执行 SQL 语句，即使你之前执行过相同的动态关联查询。

有时你可能需要给你的关联声明传递参数，以便您能更方便地执行动态关系查询。例如，您可以声明一个 bigOrders 关联如下，

中间关联表

在数据库建模中，当两个关联表之间的对应关系是多对多时，通常会引入一个连接表。例如，order 表和 item 表可以通过名为 order_item 的连接表相关联。一个 order 将关联多个 order items，而一个 order item 也会关联到多个 orders。

当声明这种表关联后，您可以调用 via() 或 viaTable() 指明连接表。via() 和 viaTable() 之间的区别是前者是根据现有的关联名称来指定连接表，而后者直接使用连接表。例如，

通过多个表来连接关联声明

通过使用 via() 方法，它还可以通过多个表来定义关联声明。再考虑考虑上面的例子，我们有 Customer，Order 和 Item 类。我们可以添加一个关联关系到 Customer 类，这个关联可以列出了 Customer（客户）的订单下放置的所有 Item（商品），这个关联命名为 getPurchasedItems()，关联声明如下代码示例所示：

延迟加载和即时加载

在访问关联数据中，我们解释说可以像问正常的对象属性那样访问 Active Record 实例的关联属性。SQL 语句仅在你第一次访问关联属性时执行。我们称这种关联数据访问方法为 延迟加载。例如，

延迟加载使用非常方便。但是，当你需要访问相同的具有多个 Active Record 实例的关联属性时，可能会遇到性能问题。请思考一下以下代码示例。有多少 SQL 语句会被执行？

你瞅瞅，上面的代码会产生 101 次 SQL 查询！这是因为每次你访问 for 循环中不同的 Customer 对象的 orders 关联属性时，SQL 语句都会被执行一次。

为了解决上述的性能问题，你可以使用所谓的 即时加载，如下所示，

通过调用 yii\db\ActiveQuery::with() 方法，你使 Active Record 在一条 SQL 语句里就返回了这 100 位客户的订单。结果就是，你把要执行的 SQL 语句从 101 减少到 2 条！

你可以即时加载一个或多个关联。你甚至可以即时加载 嵌套关联 。嵌套关联是一种在相关的 Active Record 类中声明的关联。例如，Customer 通过 orders 关联属性与 Order 相关联， Order 与 Item 通过 items 关联属性相关联。当查询 Customer 时，您可以即时加载通过嵌套关联符 orders.items 关联的 items。

以下代码展示了 with() 的各种用法。我们假设 Customer 类有两个关联 orders 和 country，而 Order 类有一个关联 items。

当即时加载一个关联，你可以通过匿名函数自定义相应的关联查询。例如，

自定义关联查询时，应该将关联名称指定为数组的键并使用匿名函数作为相应的数组的值。匿名函数将接受一个 $query 参数它用于表示这个自定义的关联执行关联查询的 yii\db\ActiveQuery 对象。在上面的代码示例中，我们通过附加一个关于订单状态的附加条件来修改关联查询。

数据库迁移

在开发和维护一个数据库驱动的应用程序时，数据库的结构会像代码一样不断演变。例如，在开发应用程序的过程中，会增加一张新表且必须得加进来；在应用程序被部署到生产环境后，需要建立一个索引来提高查询的性能等等。因为一个数据库结构发生改变的时候源代码也经常会需要做出改变， Yii 提供了一个 数据库迁移 功能，该功能可以记录数据库的变化，以便使数据库和源代码一起受版本控制。

码农公寓