本文详细介绍了在yii2框架中将现有sql查询转换为框架原生操作的两种主要方法：使用`createcommand()`执行原生sql和利用query builder构建复杂查询。通过一个嵌套集模型（nested set model）的菜单数据提取示例，我们将演示如何处理表别名、复杂的`between`条件以及排序，并提供相应的yii2代码实现，同时探讨了两种方法的适用场景、安全性及调试技巧。
在将现有应用程序迁移至Yii2框架时，一个常见的需求是将原有的SQL查询转换为Yii2的数据库操作方式。Yii2提供了强大的数据库抽象层，包括数据库访问对象（DAO）和查询构建器（Query Builder），以帮助开发者更安全、高效地与数据库交互。本文将以一个具体的嵌套集模型（Nested Set Model）查询为例，详细讲解如何在Yii2中实现此类SQL转换。
1. 理解原始SQL查询
假设我们有一个名为pages的表，用于存储网站页面内容，并采用了嵌套集模型来管理页面层级关系（通过lft和rgt字段）。我们需要提取特定父页面（page_link = 'index'）下的所有子页面作为菜单项，并按lft字段排序。原始SQL查询如下：
SELECT
node.id, node.page_title, node.page_link, node.parent_link
FROM
pages AS node, pages AS parent
WHERE
node.lft BETWEEN parent.lft AND parent.rgt
AND
parent.page_link = 'index'
ORDER BY
node.lft;这个查询的关键在于：
- 自连接（Self-Join）: pages表被两次引用，分别作为node和parent。
- BETWEEN条件: 用于判断node是否是parent的后代。
- AND条件: 过滤出特定的父页面。
- ORDER BY: 确保菜单项按正确的层级顺序排列。
2. 在Yii2中实现SQL转换
Yii2提供了两种主要方法来执行数据库查询：直接执行原生SQL（通过DAO的createCommand()）和使用查询构建器（Query Builder）。
2.1 方法一：使用 createCommand() 执行原生SQL
当遇到非常复杂或难以用查询构建器表达的SQL查询时，或者为了最大限度地保留原有SQL逻辑时，可以直接使用createCommand()方法执行原生SQL语句。
示例代码：
使用一条命令部署ProbeChain Rydberg测试网代理节点。自动注册为Agent(NodeType=1)，免gas，支持macOS/Linux/Windows。触发词：/r
<?php
namespace app\controllers;
use Yii;
use yii\web\Controller;
class PageController extends Controller
{
public function actionGetMenuViaRawSql()
{
// 原始SQL查询
$sql = "SELECT
node.id, node.page_title, node.page_link, node.parent_link
FROM
pages AS node, pages AS parent
WHERE
node.lft BETWEEN parent.lft AND parent.rgt
AND
parent.page_link = :parentLink
ORDER BY
node.lft";
// 使用createCommand()创建命令对象
$command = Yii::$app->db->createCommand($sql);
// 绑定参数以防止SQL注入
$command->bindValue(':parentLink', 'index');
// 执行查询并获取所有结果
$menuItems = $command->queryAll();
// 可以在这里处理 $menuItems 数据
// 例如：Yii::debug(print_r($menuItems, true), 'menuItems');
return $this->renderContent('<h1>菜单项 (原生SQL)</h1><pre>' . print_r($menuItems, true) . '</pre>');
}
}注意事项：
- 参数绑定: 务必使用bindValue()或bindParam()来绑定查询中的参数，而不是直接将变量拼接到SQL字符串中。这能有效防止SQL注入攻击。
- 性能: 对于复杂的原生SQL，其性能取决于数据库本身的优化，Yii2不会额外进行优化。
- 可维护性: 虽然直接，但原生SQL可能降低代码的可读性和可维护性，尤其是在团队协作中。
2.2 方法二：使用 Yii2 Query Builder 构建查询
Yii2的Query Builder提供了一种面向对象的方式来构建SQL查询，它具有更好的可读性、安全性（自动参数绑定）和数据库独立性。对于上述嵌套集查询，我们可以将其转换为Query Builder语句。
示例代码：
<?php
namespace app\controllers;
use Yii;
use yii\web\Controller;
use yii\db\Query; // 引入Query类
class PageController extends Controller
{
public function actionGetMenuViaQueryBuilder()
{
$query = new Query();
$menuItems = $query->select([
'node.id',
'node.page_title',
'node.page_link',
'node.parent_link'
])
->from(['node' => 'pages', 'parent' => 'pages']) // 定义表别名
->where('node.lft BETWEEN parent.lft AND parent.rgt') // 使用原生字符串处理BETWEEN两个字段的情况
->andWhere(['parent.page_link' => 'index']) // Yii2会自动绑定参数
->orderBy('node.lft')
->all(Yii::$app->db); // 执行查询，并指定数据库连接
// 可以在这里处理 $menuItems 数据
// 例如：Yii::debug(print_r($menuItems, true), 'menuItems');
return $this->renderContent('<h1>菜单项 (Query Builder)</h1><pre>' . print_r($menuItems, true) . '</pre>');
}
}关键点解析：
- select(): 指定要查询的字段。
- from(['alias' => 'tableName', ...]): 这是处理自连接的关键。通过传递一个关联数组，我们可以为同一个表定义不同的别名（node和parent）。
- where('raw_sql_condition'): 对于像node.lft BETWEEN parent.lft AND parent.rgt这样涉及两个字段的BETWEEN条件，直接使用原生SQL字符串作为where条件通常是最简洁和清晰的方式。
- andWhere(['column' => 'value']): 对于简单的相等条件，Query Builder会自动进行参数绑定。
- orderBy(): 指定排序规则。
- all(Yii::$app->db): 执行查询并返回所有结果。
3. 注意事项与最佳实践
-
选择合适的方法:
- Query Builder: 推荐用于大多数场景，因为它提供了更好的可读性、安全性、跨数据库兼容性，并且易于调试。
- createCommand(): 适用于非常复杂的、难以用Query Builder表达的SQL，或需要执行数据库特定函数、存储过程等。
- 安全性: 无论选择哪种方法，始终确保对用户输入的数据进行参数绑定，以防止SQL注入攻击。Query Builder会自动处理大部分情况，但使用createCommand()时需要手动绑定。
-
调试:
- Yii2 Debug Bar: 在开发环境中启用Yii2 Debug Bar，可以清晰地看到所有执行的SQL查询，包括Query Builder生成的SQL和原生SQL。这是排查查询问题最直接有效的方法。
- createCommand()->getRawSql(): 在执行查询之前，可以通过$command->getRawSql()方法获取最终生成的SQL语句，这对于调试Query Builder生成的复杂SQL非常有帮助。
- 性能优化: 确保数据库表上有适当的索引，特别是lft, rgt, page_link等字段，以提高查询效率。Yii2的数据库抽象层不会替代良好的数据库设计和索引策略。
- AR (Active Record): 对于更高级的数据库操作，Yii2还提供了Active Record，它将数据库表映射为PHP对象，提供更面向对象的CRUD操作。虽然本例中的自连接通过AR实现会更复杂，但在处理单表或简单关联时，AR是首选。
总结
在Yii2中转换SQL查询提供了灵活多样的选择。对于简单的查询，Query Builder是首选；对于涉及复杂逻辑或需要直接操作数据库的场景，createCommand()提供了强大的原生SQL执行能力。理解这两种方法的优缺点并结合实际需求进行选择，将有助于构建高效、安全且易于维护的Yii2应用程序。通过本文的嵌套集模型示例，希望您能掌握在Yii2中处理复杂SQL查询的核心技巧。
