Conventions（惯例）
Yii主张配置实现惯例。按照惯例，某人可以创建复杂的Yii应用，无需编写和维护复杂的配置。当然，在需要时几乎每一个方面，Yii仍然可以进行自定义配置。
下面我们描述建议Yii开发的惯例。 为了方便起见，我们假设WebRoot是Yii应用安装目录。
网址（URL）
默认情况下，Yii确认的网址，格式如下：
http://hostname/index.php?r=ControllerID/ActionID
r GET变量指route ，可以通过Yii解析为控制器和动作。如果ActionID省略，控制器将采用默认的动作(通过CController::defaultAction指定);如果ControllerID也省略（或r变量也没有） ，该应用程序将使用默认的控制器（通过CWebApplication::defaultController定义） 。
CUrlManager的帮助下，有可能生成和识别更多的搜索引擎优化友好的URL ，如http://hostname/ControllerID/ActionID.html。此功能详细情况在URL Management。
代码（Code）
Yii建议变量，函数和类类型使用骆驼方式命名，就是不用空格连接每个名字的单词。变量和函数名首字母小写，为了区分于类名称（如：$basePath ，runController（），LinkPager）。私有类成员变量，建议将他们的名字前缀加下划线字符（例如：$ _actionList） 。
因为在PHP5.3.0之前不支持命名空间，建议以一些独特的方式命名这些类，以避免和第三方名称冲突。出于这个原因，所有Yii框架类开头的字母"C"。
控制器类名的特别规则是，他们必须附上Controller后缀 。控制器的ID，然后定义为类名称首字母小写和Controller结尾。 例如，PageController类将有ID page 。这条规则使得应用更加安全。这也使得相关的网址控制器更加简洁（例如/index.php?r=page/index替代/index.php?r=PageController/index） 。
配置（Configuration）
配置是数组关键值对。每个键代表对象名称属性的配置，每个值相应属性的初始值。举个例子，array('name'=>'My application', 'basePath'=>'./protected')初始 name 和basePath 属性为其相应的数组值。
一个对象任何写入属性可以配置。如果没有配置，属性将使用它们的默认值。当设定属性，应该阅读相应的文件，以便使初始值设定正确。
文件（File）
文件命名和使用惯例取决于其类型。
类文件应命名应使用包含的公共类名字。例如，CController 类是在CController.php 文件。公共类是一个可用于任何其他类的类。每个类文件应包含最多一个公共类。私有类（类只能用于一个单一的公共类）可能和公有类存放在同一个文件。
视图文件应使用视图名称命名。例如，index视图在index.php文件里。视图文件是一个PHP脚本文件包含HTML和PHP代码，主要用来显示的。
配置文件可任意命名。配置文件是一个PHP脚本的唯一宗旨就是要返回一个关联数组代表配置。
目录（Directory）
Yii假定默认设置的目录用于各种目的。如果需要的话，他们每个可自定义。
· WebRoot/protected: 这是application base directory 包括所有安全敏感的PHP脚本和数据文件。Yii有一个默认的别名为application代表此路径。这个目录和下面的一切文件目录，将得到保护不被网络用户访问。它可通过CWebApplication::basePath自定义。
· WebRoot/protected/runtime: 此目录拥有应用程序在运行时生成的私有临时文件。这个目录必须可被Web服务器进程写。它可通过CApplication::runtimePath定制。
· WebRoot/protected/extensions: 此目录拥有所有第三方扩展。它可通过CApplication::extensionPath定制。
· WebRoot/protected/modules: 此目录拥有所有应用modules，每个代表作为一个子目录。
· WebRoot/protected/controllers: 此目录拥有所有控制器类文件。它可通过CWebApplication::controllerPath定制。
· WebRoot/protected/views:此目录包括所有的视图文件，包括控制视图，布局视图和系统视图。可通过CWebApplication::viewPath定制。
· WebRoot/protected/views/ControllerID: 此目录包括某个控制类的视图文件。这里 ControllerID 代表控制类的ID。可通过CController::getViewPath定制。
· WebRoot/protected/views/layouts: 此目录包括所有的布局视图文件。可通过CWebApplication::layoutPath来定制。
· WebRoot/protected/views/system: 此目录包括所有的系统视图文件。系统视图文件是显示错误和例外的模板。可通过CWebApplication::systemViewPath定制。
· WebRoot/assets: 此目录包括发布的asset文件。一个asset文件是一个私有文件，可能被发布来被Web用户访问。此目录必须Web服务进程可写。可通过CAssetManager::basePath定制。
· WebRoot/themes: 此目录包括各种适用于应用程序的主题。每个子目录代表一个主题，名字为子目录名字。可通过CThemeManager::basePath定制。
开发流程
已经描述了yii的基本概念，现在我们看看用yii开发一个web程序的基本流程。前提是我这个程序我们已经做了需求分析和必要的设计分析。
1. 创建目录结构。在前面的章节Creating First Yii Application写的yiic工具可以帮助我们快速完成这步。
2. 配置 application。就是修改application配置文件。这步有可能会写一些application部件(例如：用户部件)
3. 每种类型的数据都创建一个 model 类来管理。 同样，yiic可以为我们需要的数据库表自动生成active record active record 类。
4. 每种类型的用户请求都创建一个 controller 类。 依据实际的需求对用户请求进行分类。一般来说，如果一个model类需要用户访问，就应该对应一个controller类。yiic工具也能自动完成这步。
5. 实现 actions 和相应的 views。这是真正需要我们编写的工作。
6. 在controller类里配置需要的action filters 。
7. 如果需要主题功能，编写 themes。
8. 如果需要 internationalization国际化功能，编写翻译语句。
9. 使用 caching 技术缓存数据和页面。
10. 最后 tune up调整程序和发布。
以上每个步骤，有可能需要编写测试案例来测试。
使用表单
通过HTML表单收集用户数据是Web程序开发的主要工作.而设计表单,开发者往往需要使用已存在的数据或者默认值来填充表单,用以验证用户输入,为无效的输入展示恰当的错误信息,然后保存数据到持久存储器.Yii 使用了它的 MVC 架构,大大简化了这个工作流程.
使用 Yii 通常要按以下步骤来处理表单 :
1. 创建一个模型类来描述需要被收集的数据字段;
2. 创建一个控制器动作代码来响应提交的表单;
3. 在视图脚本里创建一个表单关联到控制器动作.
在下一节,我们将详细介绍这些步骤的具体实现.
创建模型
在编写我们所需要的表单的HTML代码之前,我们先得决定我们要从用户那里获取什么样的数据,这些数据需要遵从怎样的规则.模型类可以用于记录这些信息.模型,作为已定义的 Model 的一部分,是用来保存,校验用户输入的核心.
根据用户输入的用途,我们可以创建两类模型.如果用户的输入被收集,使用然后被丢弃了, 我们应该创建一个 form model 模型;如果用户的数据被收集, 然后保存到数据库,我们则应该选择使用 active record 模型. 这两种模型共享着定义了表单所需通用界面的基类 CModel.
注意: 本章中我们主要使用表单模型的示例. 它也同样适用于 active record 模型.
定义模型类
以下我们会创建一个 LoginForm 模型类从一个登录页面收集用户数据.因为登录数据只用于校验用户而不需要保存,所以我们创建的 LoginForm 是一个表单模型.
class LoginForm extends CFormModel
{
public $username;
public $password;
public $rememberMe=false;
}
LoginForm 声明了三个属性:$username, $password 和 $rememberMe.他们用于保存用户输入的用户名,密码以及用户是否想记住它登录状态的选项.因为 $rememberMe 有一个默认值 false ,其相应的选项框在表单初始化显示时是没有被选中的.
说明: 为了替代这些成员变量"属性"的叫法,我们使用 特性 一词来区分于普通属性.特性是一种 主要用于储存用户输入数据或数据库读取数据的属性.
声明有效的规则
一旦用户提交了他的表单,模型获得了到位的数据,在使用数据前我们需要确定输入是否是有效的.这个过程被一系列针对输入有效性的校验规则来校验.我们应该在返回一个规则配置数组的 rules() 方法中指定这一有效的规则.
class LoginForm extends CFormModel
{
public $username;
public $password;
public $rememberMe=false;
public function rules()
{
return array(
array('username, password', 'required'),
array('password', 'authenticate'),
);
}
public function authenticate($attribute,$params)
{
if(!$this->hasErrors()) // 我们只想校验没有输入错误
{
$identity=new UserIdentity($this->username,$this->password);
if($identity->authenticate())
每个通过 rules() 返回的规则必须遵照以下格式:
array('AttributeList', 'Validator', 'on'=>'ScenarioList', ...附加选项)
AttributeList 是需要通过规则校验的以逗号分隔的特性名称集的字符串; 校验器(Validator) 指定了使用哪种校验方式; on 参数是规则在何种情况下生效的场景列表;附加选项是用来初 始化相应校验属性值的"名称-值"的配对.
在一个校验规则中有三种方法可以指定 校验器 . 第一, 校验器 可以是模型类中的一个方法的名称,就像以上例子中的 authenticate . 校验方法必须是以下结构 :
/**
* @param string 用于校验特性
* @param array 指定了校验规则
*/
public function ValidatorName($attribute,$params) { ... }
第二, 校验器 可以是一个校验类的名称.当规则生效时,校验类的实例将被创建用于执行实际的校验. 规则里的附加选项用于初始化实例中属性的初始值.校验类必须继承自 CValidator.
注意: 当为一个active record指定规则时,我们可以使用名称为 on 的特别选项.这个选项可以 是'insert' 或者 'update' 以便只有当插入或者更新记录时,规则才会生效.如果没有设置,规则 将在任何 save() 被调用的时候生效.
第三 , Validator 可以是一个指向一个预定义的校验类的别名.在以上的例子中, required 指向了 CRequiredValidator ,它确保了特性的有效值不能为空.以下是预定义校验别名的一份完整的列表:
· captcha: CCaptchaValidator 的别名,确保了特性的值等于 CAPTCHA 显示出来的验证码.
· compare: CCompareValidator 的别名, 确保了特性的值等于另一个特性或常量.
· email: CEmailValidator 的别名,确保了特性的值是一个有效的电邮地址.
· default: CDefaultValueValidator 的别名, 为特性指派了一个默认值.
· file: CFileValidator 的别名, 确保了特性包含了一个上传文件的名称.
· filter: CFilterValidator 的别名, 使用一个过滤器转换特性的形式.
· in: CRangeValidator 的别名, 确保了特性出现在一个预订的值列表里.
· length: CStringValidator 的别名, 确保了特性的长度在指定的范围内.
· match: CRegularExpressionValidator 的别名, 确保了特性匹配一个正则表达式.
· numerical: CNumberValidator 的别名, 确保了特性是一个有效的数字.
· required: CRequiredValidator 的别名, 确保了特性不为空.
· type: CTypeValidator 的别名, 确保了特性为指定的数据类型.
· unique: CUniqueValidator 的别名, 确保了特性在数据表字段中是唯一的.
· url: CUrlValidator 的别名, 确保了特性是一个有效的路径.
以下我们列出了使用预定义校验器的例子:
// username 不为空
array('username', 'required'),
// username 必须大于 3 小于 12 字节
array('username', 'length', 'min'=>3, 'max'=>12),
// 在注册场景中, password 必须和 password2 一样
array('password', 'compare', 'compareAttribute'=>'password2', 'on'=>'register'),
// 在登录场景中, password 必须被校验
array('password', 'authenticate', 'on'=>'login'),
安全的特性分配
注意: 自 1.0.2 版起,基于场景的特性分配开始生效.
在一个模型实例被创建之后,我们经常需要使用用户提交的数据归位它的特性.这将大大简化以下繁重的任务:
$model=new LoginForm;
if(isset($_POST['LoginForm']))
$model->setAttributes($_POST['LoginForm'], 'login');
以上的是一个繁重的任务,它在 login 场景(第二给参数指定的)中为每个 $_POST['LoginForm'] 数据项分配对应的模型特性.而它和以下的代码效果是一样的:
foreach($_POST['LoginForm'] as $name=>$value)
{
if($name is a safe attribute)
$model->$name=$value;
}
决定一个数据项是否是安全的,基于一个名为 safeAttributes 方法的返回值和数据项被指定的场景. 默认的,这个方法返回所有公共成员变量作为 CFormModel 的安全特性,而它也返回了除了主键外, 表中所有字段名作为 CActiveRecord 的特性.我们可以根据场景重写这个方法来限制安全特性 .例如, 一个用户模型可以包含很多特性,但是在 login 场景.里,我们只能使用 username 和 password 特性.我们可以按照如下来指定这一限制 :
public function safeAttributes()
{
return array(
parent::safeAttributes(),
'login' => 'username, password',
);
}
safeAttributes 方法更准确的返回值应该是如下结构的 :
array(
//这些属性可以在任意场景被大量分配的
//以下特性并没有被明确的分配
'attr1, attr2, ...',
*
//以下特性只可以在场景1中被大量分配的
'scenario1' => 'attr2, attr3, ...',
*
//以下特性只可以在场景2中被大量分配的
'scenario2' => 'attr1, attr3, ...',
)
如果模型不是场景敏感的(比如,它只在一个场景中使用,或者所有场景共享了一套同样的安全特性),返回值可以是如下那样简单的字符串.
'attr1, attr2, ...'
而那些不安全的数据项,我们需要使用独立的分配语句来分配它们到相应的特性.如下所示:
$model->permission='admin';
$model->id=1;
触发校验
一旦用户提交的数据到位,我们可以调用 CModel::validate() 来触发数据校验处理.这个方法返回了一个指示校验是否成功的值. 而 CActiveRecord 中的校验可以在我们调用它的 CActiveRecord::save() 方法时自动触发.
当我们调用 CModel::validate() 方法, 我们可以指定一个场景参数.只有在特定的场景下校验规则才会生效.校验规则会在那些 on 选项没有被设置或者包含了指定的场景名称的场景中生效.如果我们没有指定场景,而调用了 CModel::validate() 方法,只有那些 on 选项没有设置的规则才会被执行.
例如,在注册一个用户时,我们运行以下脚本来执行校验 :
$model->validate('register');
我们可以按以下在表单模型里声明校验规则:
public function rules()
{
return array(
array('username, password', 'required'),
array('password_repeat', 'required', 'on'=>'register'),
array('password', 'compare', 'on'=>'register'),
);
}
结果是,第一条规则在所有场景生效,而接下来的两条规则只有在 register 场景中生效.
注意: 自 1.0.2 版起,基于场景的校验开始生效.
检索校验错误
我们可以使用 CModel::hasErrors() 来检查是否有校验错误,如果是,我们可以使用 CModel::getErrors() 来获取错误信息. 上述两者中的任何一个方法都可以用于所有特性或者单独的一个特性.
特性标签
当设计一个表单时,我们通常需要为每个输入字段显示标签. 标签告诉了用户他被期望输入哪种信息.尽管我们可以在视图里使用硬性编码,但是如果我们在对应的模型里指定了标签,那么它将提供更强的弹性和更好的便利性.
CModel 会默认的返回特性的名称作为特性的标签.而通过重写 attributeLabels() 方法,可以实现标签的定制.在接下来章节中我们将看到,在模型里指定标签将允许我们创建一个更快捷更强大的表单.
编写ACTION
一旦有了model，我们可以开始编写操作model的逻辑。我们把这些逻辑放在controller action里面。用录入登陆表单这个例子来说明，如下是需要的代码：
public function actionLogin()
{
$form=new LoginForm;
if(isset($_POST['LoginForm']))
{
// 收集用户输入的数据
$form->attributes=$_POST['LoginForm'];
// 验证用户输入，如果无效则重定位到前个页面
if($form->validate())
$this->redirect(Yii::app()->user->returnUrl);
}
// 显示登陆表单
$this->render('login',array('user'=>$form));
}
上面写的是，我们编写LoginForm实例；如果请求是POST方式（意味着登陆表单是submit），我们产生一个$form，里面放着提交过来的数据 $_POST['LoginForm'];然后验证输入，如果成功，把用户请求url定位到相应需要授权的页面。如果验证失败，或者是第一次访问login页面的，把用户请求url定位到login的页面，login页面具体怎么写会在下一个小节里描写。
提示: 在login action里面，我们用 Yii::app()->user->returnUrl 获取之前需要验证的url。表达式Yii::app()->user返回一个CWebUser的实例， 它主要用来存放用户session信息的（例如：用户名，状态等）。想要了解更多，看身份验证和授权这章。
大家注意这段在login action里面的php语句：
$form->attributes=$_POST['LoginForm'];
真如我们在Securing Attribute Assignments提到，这句话只是创建一个model存放用户提交来的数据。CModel里面以name-value数组形式定义了attributes属性，每个value被分配到相应的name属性上。所以如果$_POST['LoginForm']给了我们这样的数组，上面的代码将等同于后面的这长串代码（假设每个需要的属性这个数组都提供）：
$form->username=$_POST['LoginForm']['username'];
$form->password=$_POST['LoginForm']['password'];
$form->rememberMe=$_POST['LoginForm']['rememberMe'];
提示: 为了让$_POST['LoginForm']不提供字符串而是数组，根据惯例view页面的输入字段应该写model相应的名字。记住是，一个页面输入字段对应model（简称C）里面的一个属性C[a]。例如，我们用 LoginForm[username]去命名页面username输入字段。
剩下的工作是编写login view了，编写里面的html表单和相应的输入字段。
创建表单
编写 login 视图是直截了当的.我们以 form 标签开头,form 标签的 action 属性应该是 login 行为之前描述的的 URL .然后我们插入在 LoginForm 类中声明过的标签和文本框.最后我们插入一个用于用户点击后提交表单的按钮.所有这些都可以使用纯HTML代码来完成.
Yii 提供了一些辅助器(helper)类来简化视图组合.例如,创建一个文本输入框,我们可以调用 CHtml::textField() ;创建一个下拉菜单,则可调用 CHtml::dropDownList() .
信息: 人们可能不知道在编写类似代码时使用辅助器比使用纯HTML编写代码好处是什么.例如,如下代码将生成一个当其值被用户改变时可以触发表单提交的文本输入框.
CHtml::textField($name,$value,array('submit'=>''));
否则在任何需要的地方都要写上那笨拙的JavaScript了.
如下, 我们使用 CHtml 来创建登陆表单. 我们假设变量 $user 代表 LoginForm 的实例.
<div class="yiiForm">
<?php echo CHtml::form(); ?>
<?php echo CHtml::errorSummary($user); ?>
<div class="simple">
<?php echo CHtml::activeLabel($user,'username'); ?>
<?php echo CHtml::activeTextField($user,'username') ?>
</div>
<div class="simple">
<?php echo CHtml::activeLabel($user,'password'); ?>
<?php echo CHtml::activePasswordField($user,'password')
?>
</div>
<div class="action">
<?php echo CHtml::activeCheckBox($user,'rememberMe'); ?>
记住我?<br/>
<?php echo CHtml::submitButton('Login'); ?>
</div>
</form>
</div><!-- yii表单 -->
以上代码生成了一个更动态的表单. 例如 CHtml::activeLabel() 生成了一个关联到指定模型特性的标签.如果这个特性有一个输入错误,标签 CSS 的 class 将变成改变标签视觉表现到相应 CSS 样式的 error.类似的,[CHtml::activeTextField() 为指定的模型特性生成了一个文本输入框,其CSS 的 class 也会在发生任何错误时变成 error.
如果我们使用了 yiic 脚本提供的CSS样式文件 form.css,那么生成的表单和如下显示的差不多 :
登陆页面
登陆出错页面
收集表格输入
有时候我们想按批收集用户输入.也就是,用户可以为多个模型实例输入信息然后一次性提交全部.我们之所以把这个称之为 表格输入(tabular input) 是因为输入的字段通常出现在一个HTML表格里.
要使用表格输入,我们首先需要使用模型实例创建或者填充一个数组,这取决于我们是插入还是更新数据.然后我们从$_POST 变量里取出用户输入的数据,再将他们分配到各个模型中.这和从单模型输入中取出数据有一点微小的差异,那就是我们使用 $_POST['ModelClass'][$i] 取出数据而不是 $_POST['ModelClass'].
public function actionBatchUpdate()
{
// 批处理模式中,收集用于更新的项
// 假定每项都是模型类 'Item' 的
$items=$this->getItemsToUpdate();
if(isset($_POST['Item']))
{
$valid=true;
foreach($items as $i=>$item)
{
if(isset($_POST['Item'][$i]))
$item->attributes=$_POST['Item'][$i];
$valid=$valid && $item->validate();
}
if($valid) // 所有的项都是有效的
// ...在这里干点什么
}
// 显示视图收集表格输入
$this->render('batchUpdate',array('items'=>$items));
}
准备好了动作,我们需要 batchUpdate 视图在一个 HTML 表中显示输入框.
<div class="yiiForm">
<?php echo CHtml::form(); ?>
<table>
<tr><th>名称</th><th>价格</th><th>数量</th><th>描述</th></tr>
<?php foreach($items as $i=>$item): ?>
<tr>
<td><?php echo CHtml::activeTextField($item,"name[$i]"); ?></td>
<td><?php echo CHtml::activeTextField($item,"price[$i]"); ?></td>
<td><?php echo CHtml::activeTextField($item,"count[$i]"); ?></td>
<td><?php echo CHtml::activeTextArea($item,"description[$i]"); ?></td>
</tr>
<?php endforeach; ?>
</table>
<?php echo CHtml::submitButton('Save'); ?>
</form>
</div><!-- yii表单 -->
注意 : 在上述代码中,我们使用了 "name[$i]" 代替了 "name" 来作为 CHtml::activeTextField 的第二参数.
如果有任何校验错误,那么对应的字段将会自动高亮,就像我们先前提到的单模型输入一样.
Working with Database(数据库开发工作)
Yii提供了强大的数据库编程支持。Yii数据访问对象(DAO)建立在PHP的数据对象(PDO)extension上，使得在一个单一的统一的接口可以访问不同的数据库管理系统(DBMS)。使用Yii的DAO开发的应用程序可以很容易地切换使用不同的数据库管理系统，而不需要修改数据访问代码。Yii 的Active Record（ AR ），实现了被广泛采用的对象关系映射(ORM)办法，进一步简化数据库编程。按照约定，一个类代表一个表，一个实例代表一行数据。Yii AR消除了大部分用于处理CRUD（创建，读取，更新和删除）数据操作的sql语句的重复任务。
尽管Yii的DAO和AR能够处理几乎所有数据库相关的任务，您仍然可以在Yii application中使用自己的数据库库。事实上，Yii框架精心设计使得可以与其他第三方库同时使用。
Data Access Objects (DAO)
Data Access Objects (DAO) provides a generic API to access data stored in different database management systems (DBMS). As a result, the underlying DBMS can be changed to a different one without requiring change of the code which uses DAO to access the data.
数据访问对象（DAO）针对不同的数据库管理系统提供一个通用的的数据访问API。因此，使用DAO将数据访问移植到其他数据库时，几乎不需要做改动。
Yii DAO is built on top of PHP Data Objects (PDO) which is an extension providing unified data access to many popular DBMS, such as MySQL, PostgreSQL. Therefore, to use Yii DAO, the PDO extension and the specific PDO database driver (e.g. PDO_MYSQL) have to be installed.
Yii DAO mainly consists of the following four classes:
· CDbConnection: represents a connection to a database. 声明数据库连接
· CDbCommand: represents an SQL statement to execute against a database. 声明一个对数据库执行的SQL语句
· CDbDataReader: represents a forward-only stream of rows from a query result set. 声明一个从查询结果集中的前瞻性行数据流
· CDbTransaction: represents a DB transaction. 表示一个DB事务
In the following, we introduce the usage of Yii DAO in different scenarios.
如下所述，我们将用不同的章节来介绍如何使用Yii DAO
Establishing Database Connection
To establish a database connection, create a CDbConnection instance and activate it. A data source name (DSN) is needed to specify the information required to connect to the database. A username and password may also be needed to establish the connection. An exception will be raised in case an error occurs during establishing the connection (e.g. bad DSN or invalid username/password).
为了建立一个数据库连接，创建一个CDbConnection实体并激活它。需要申明一个连接到数据库的数据源名（DSN）。需要建立连接的用户名和密码。在建立连接过程中的需要申明一个错误异常（例如，错误的数据源或用户名/密码无效）
$connection=new CDbConnection($dsn,$username,$password);
// establish connection. You may try...catch possible exceptions
$connection->active=true;
......
$connection->active=false; // close connection
The format of DSN depends on the PDO database driver in use. In general, a DSN consists of the PDO driver name, followed by a colon, followed by the driver-specific connection syntax. See PDO documentation for complete information. Below is a list of commonly used DSN formats:
数据源DSN的格式依赖PDO数据库驱动建立。一般来说，
· SQLite: sqlite:/path/to/dbfile
· MySQL: mysql:host=localhost;dbname=testdb
· PostgreSQL: pgsql:host=localhost;port=5432;dbname=testdb
· SQL Server: mssql:host=localhost;dbname=testdb
· Oracle: oci:dbname=//localhost:1521/testdb
Because CDbConnection extends from CApplicationComponent, we can also use it as an application component. To do so, configure in a db (or other name) application component in the application configuration as follows,
array(
......
'components'=>array(
......
'db'=>array(
'class'=>'CDbConnection',
'connectionString'=>'mysql:host=localhost;dbname=testdb',
'username'=>'root',
'password'=>'password',
'emulatePrepare'=>true, // needed by some MySQL installations
),
),
)
We can then access the DB connection via Yii::app()->db which is already activated automatically, unless we explictly configure CDbConnection::autoConnect to be false. Using this approach, the single DB connection can be shared in multiple places in our code.
Executing SQL Statements
Albert 2010年4月26日 0:37:57
Once a database connection is established, SQL statements can be executed using CDbCommand. One creates a CDbCommand instance by calling CDbConnection::createCommand() with the specified SQL statement:
$command=$connection->createCommand($sql);
// if needed, the SQL statement may be updated as follows:
// $command->text=$newSQL;
A SQL statement is executed via CDbCommand in one of the following two ways:
· execute(): performs a non-query SQL statement, such as INSERT, UPDATE and DELETE. If successful, it returns the number of rows that are affected by the execution.
· query(): performs an SQL statement that returns rows of data, such as SELECT. If successful, it returns a CDbDataReader instance from which one can traverse the resulting rows of data. For convenience, a set of queryXXX() methods are also implemented which directly return the query results.
An exception will be raised if an error occurs during the execution of SQL statements.
$rowCount=$command->execute(); // execute the non-query SQL
$dataReader=$command->query(); // execute a query SQL
$rows=$command->queryAll(); // query and return all rows of result
$row=$command->queryRow(); // query and return the first row of result
$column=$command->queryColumn(); // query and return the first column of result
$value=$command->queryScalar(); // query and return the first field in the first row
Fetching Query Results
After CDbCommand::query() generates the CDbDataReader instance, one can retrieve rows of resulting data by calling CDbDataReader::read() repeatedly. One can also use CDbDataReader in PHP's foreach language construct to retrieve row by row.
$dataReader=$command->query();
// calling read() repeatedly until it returns false
while(($row=$dataReader->read())!==false) { ... }
// using foreach to traverse through every row of data
foreach($dataReader as $row) { ... }
// retrieving all rows at once in a single array
$rows=$dataReader->readAll();
Note: Unlike query(), all queryXXX() methods return data directly. For example, queryRow() returns an array representing the first row of the querying result.
Using Transactions
When an application executes a few queries, each reading and/or writing information in the database, it is important to be sure that the database is not left with only some of the queries carried out. A transaction, represented as a CDbTransaction instance in Yii, may be initiated in this case:
· Begin the transaction.
· Execute queries one by one. Any updates to the database are not visible to the outside world.
· Commit the transaction. Updates become visible if the transaction is successful.
· If one of the queries fails, the entire transaction is rolled back.
The above workflow can be implemented using the following code:
$transaction=$connection->beginTransaction();
try
{
$connection->createCommand($sql1)->execute();
$connection->createCommand($sql2)->execute();
//.... other SQL executions
$transaction->commit();
}
catch(Exception $e) // an exception is raised if a query fails
{
$transaction->rollBack();
}
Binding Parameters
To avoid SQL injection attacks and to improve performance of executing repeatedly used SQL statements, one can "prepare" an SQL statement with optional parameter placeholders that are to be replaced with the actual parameters during the parameter binding process.
为了防止SQL注入攻击和迅速提升SQL语句的执行性能
The parameter placeholders can be either named (represented as unique tokens) or unnamed (represented as question marks). Call CDbCommand::bindParam() or CDbCommand::bindValue() to replace these placeholders with the actual parameters. The parameters do not need to be quoted: the underlying database driver does it for you. Parameter binding must be done before the SQL statement is executed.
// an SQL with two placeholders ":username" and ":email"
$sql="INSERT INTO users(username, email) VALUES(:username,:email)";
$command=$connection->createCommand($sql);
// replace the placeholder ":username" with the actual username value
$command->bindParam(":username",$username,PDO::PARAM_STR);
// replace the placeholder ":email" with the actual email value
$command->bindParam(":email",$email,PDO::PARAM_STR);
$command->execute();
// insert another row with a new set of parameters
$command->bindParam(":username",$username2,PDO::PARAM_STR);
$command->bindParam(":email",$email2,PDO::PARAM_STR);
$command->execute();
The methods bindParam() and bindValue() are very similar. The only difference is that the former binds a parameter with a PHP variable reference while the latter with a value. For parameters that represent large block of data memory, the former is preferred for performance consideration.
For more details about binding parameters, see the relevant PHP documentation.
Binding Columns
When fetching query results, one can also bind columns with PHP variables so that they are automatically populated with the latest data each time a row is fetched.
$sql="SELECT username, email FROM users";
$dataReader=$connection->createCommand($sql)->query();
// bind the 1st column (username) with the $username variable
$dataReader->bindColumn(1,$username);
// bind the 2nd column (email) with the $email variable
$dataReader->bindColumn(2,$email);
while($dataReader->read()!==false)
{
// $username and $email contain the username and email in the current row
}
Using Table Prefix
Starting from version 1.1.0, Yii provides integrated support for using table prefix. Table prefix means a string that is prepended to the names of the tables in the currently connected database. It is mostly used in a shared hosting environment where multiple applications share a single database and use different table prefixes to differentiate from each other. For example, one application could use tbl_ as prefix while the other yii_.
从V1.1.0开始，Yii集成支持使用表前缀。表前缀意味在当前连接数据中设置表名。它被经常用于多个应用中共享一个数据库，然后通过使用不同的表前缀来区分他们。例如，一个当其他应用使用Yii_前缀时，另外一个使用tbl_作为前缀。
To use table prefix, configure the CDbConnection::tablePrefix property to be the desired table prefix. Then, in SQL statements use {{TableName}} to refer to table names, where TableName means the table name without prefix. For example, if the database contains a table named tbl_users where tbl_ is configured as the table prefix, then we can use the following code to query about users:
为使用表前缀，配置CDbConnection::tablePrefix属性使其。然后，在SQL语句中使用{{TableName}}用于指向不同的表名。当TableName 意味着不使用表前缀。例如，如果一个数据库中当表名为tbl_name的表，tbl_被配置为表前缀的时候，然后我们使用如下代码来查询相关用户：
$sql='SELECT * FROM {{users}}';
$users=$connection->createCommand($sql)->queryAll();
Active Record
Although Yii DAO can handle virtually any database-related task, chances are that we would spend 90% of our time in writing some SQL statements which perform the common CRUD (create, read, update and delete) operations. It is also difficult to maintain our code when they are mixed with SQL statements. To solve these problems, we can use Active Record.
通过Yii DAO能控制需求虚拟任何一个数据库关系任务
Active Record (AR) is a popular Object-Relational Mapping (ORM) technique. Each AR class represents a database table (or view) whose attributes are represented as the AR class properties, and an AR instance represents a row in that table. Common CRUD operations are implemented as AR methods. As a result, we can access our data in a more object-oriented way. For example, we can use the following code to insert a new row to the Post table:
$post=new Post;
$post->title='sample post';
$post->content='post body content';
$post->save();
In the following we describe how to set up AR and use it to perform CRUD operations. We will show how to use AR to deal with database relationships in the next section. For simplicity, we use the following database table for our examples in this section. Note that if you are using MySQL database, you should replace AUTOINCREMENT with AUTO_INCREMENT in the following SQL.
CREATE TABLE Post (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
title VARCHAR(128) NOT NULL,
content TEXT NOT NULL,
createTime INTEGER NOT NULL
);
Note: AR is not meant to solve all database-related tasks. It is best used for modeling database tables in PHP constructs and performing queries that do not involve complex SQLs. Yii DAO should be used for those complex scenarios.
Establishing DB Connection
AR relies on a DB connection to perform DB-related operations. By default, it assumes that the db application component gives the needed CDbConnection instance which serves as the DB connection. The following application configuration shows an example:
return array(
'components'=>array(
'db'=>array(
'class'=>'system.db.CDbConnection',
'connectionString'=>'sqlite:path/to/dbfile',
// turn on schema caching to improve performance
// 'schemaCachingDuration'=>3600,
),
),
);
Tip: Because Active Record relies on the metadata about tables to determine the column information, it takes time to read the metadata and analyze it. If the schema of your database is less likely to be changed, you should turn on schema caching by configuring the CDbConnection::schemaCachingDuration property to be a value greater than 0.
Support for AR is limited by DBMS. Currently, only the following DBMS are supported:
· MySQL 4.1 or later
· PostgreSQL 7.3 or later
· SQLite 2 and 3
· Microsoft SQL Server 2000 or later
· Oracle
Note: The support for Microsoft SQL Server has been available since version 1.0.4; And the support for Oracle has been available since version 1.0.5.
If you want to use an application component other than db, or if you want to work with multiple databases using AR, you should override CActiveRecord::getDbConnection(). The CActiveRecord class is the base class for all AR classes.
如果你想使用一个数据库应用程序组件，或者你想用AR来支持多个数据库，你应该替换CActiveRecord::getDbConnection()。CActiveRecord类是一个对所有AR类的基础类。
Tip: There are two ways to work with multiple databases in AR. If the schemas of the databases are different, you may create different base AR classes with different implementation of getDbConnection(). Otherwise, dynamically changing the static variable CActiveRecord::db is a better idea.
技巧：使用AR可以有两种方法工作与多数据库上。数据库中的schemas是不同的，你可以用不同的f getDbConnection()执行类创建不同的AR类。否则，动态改变CActiveRecord::db静态变量也是一个好主意。
Defining AR Class
To access a database table, we first need to define an AR class by extending CActiveRecord. Each AR class represents a single database table, and an AR instance represents a row in that table. The following example shows the minimal code needed for the AR class representing the Post table.
class Post extends CActiveRecord
{
public static function model($className=__CLASS__)
{
return parent::model($className);
}
}
Tip: Because AR classes are often referenced in many places, we can import the whole directory containing the AR class, instead of including them one by one. For example, if all our AR class files are under protected/models, we can configure the application as follows:
return array(
'import'=>array(
'application.models.*',
),
);
By default, the name of the AR class is the same as the database table name. Override the tableName() method if they are different. The model() method is declared as such for every AR class (to be explained shortly).
默认，AR类的名称与数据库表明一致。如果他们不同需要改写tableName()方法。
Info: To use the table prefix feature introduced in version 1.1.0, the tableName() method for an AR class may be overridden as follows,
public function tableName()
{
return '{{post}}';
}
That is, instead of returning the fully qualified table name, we return the table name without the prefix and enclose it in double curly brackets.
Column values of a table row can be accessed as properties of the corresponding AR class instance. For example, the following code sets the title column (attribute):
$post=new Post;
$post->title='a sample post';
Although we never explicitly declare the title property in the Post class, we can still access it in the above code. This is because title is a column in the Post table, and CActiveRecord makes it accessible as a property with the help of the PHP __get() magic method. An exception will be thrown if we attempt to access a non-existing column in the same way.
Info: In this guide, we name columns using camel cases (e.g. createTime). This is because columns are accessed in the way as normal object properties which also uses camel-case naming. While using camel case does make our PHP code look more consistent in naming, it may introduce case-sensitivity problem for some DBMS. For example, PostgreSQL treats column names as case-insensitive by default, and we must quote a column in a query condition if the column contains mixed-case letters. For this reason, it may be wise to name columns (and also tables) only in lower-case letters (e.g. create_time) to avoid any potential case-sensitivity issues.
Creating Record
To insert a new row into a database table, we create a new instance of the corresponding AR class, set its properties associated with the table columns, and call the save() method to finish the insertion.
$post=new Post;
$post->title='sample post';
$post->content='content for the sample post';
$post->createTime=time();
$post->save();
If the table's primary key is auto-incremental, after the insertion the AR instance will contain an updated primary key. In the above example, the id property will reflect the primary key value of the newly inserted post, even though we never change it explicitly.
If a column is defined with some static default value (e.g. a string, a number) in the table schema, the corresponding property in the AR instance will automatically has such a value after the instance is created. One way to change this default value is by explicitly declaring the property in the AR class:
class Post extends CActiveRecord
{
public $title='please enter a title';
......
}
$post=new Post;
echo $post->title; // this would display: please enter a title
Starting from version 1.0.2, an attribute can be assigned a value of CDbExpression type before the record is saved (either insertion or updating) to the database. For example, in order to save a timestamp returned by the MySQL NOW() function, we can use the following code:
$post=new Post;
$post->createTime=new CDbExpression('NOW()');
// $post->createTime='NOW()'; will not work because
// 'NOW()' will be treated as a string
$post->save();
Tip: While AR allows us to perform database operations without writing cumbersom SQL statements, we often want to know what SQL statements are executed by AR underneath. This can be achieved by turning on the logging feature of Yii. For example, we can turn on CWebLogRoute in the application configuration, and we will see the executed SQL statements being displayed at the end of each Web page. Since version 1.0.5, we can set CDbConnection::enableParamLogging to be true in the application configuration so that the parameter values bound to the SQL statements are also logged.
Reading Record
To read data in a database table, we call one of the find methods as follows.
// find the first row satisfying the specified condition
$post=Post::model()->find($condition,$params);
// find the row with the specified primary key
$post=Post::model()->findByPk($postID,$condition,$params);
// find the row with the specified attribute values
$post=Post::model()->findByAttributes($attributes,$condition,$params);
// find the first row using the specified SQL statement
$post=Post::model()->findBySql($sql,$params);
In the above, we call the find method with Post::model(). Remember that the static method model() is required for every AR class. The method returns an AR instance that is used to access class-level methods (something similar to static class methods) in an object context.
If the find method finds a row satisfying the query conditions, it will return a Post instance whose properties contain the corresponding column values of the table row. We can then read the loaded values like we do with normal object properties, for example, echo $post->title;.
The find method will return null if nothing can be found in the database with the given query condition.
When calling find, we use $condition and $params to specify query conditions. Here $condition can be string representing the WHERE clause in a SQL statement, and $params is an array of parameters whose values should be bound to the placeholders in $condition. For example,
// find the row with postID=10
$post=Post::model()->find('postID=:postID', array(':postID'=>10));
Note: In the above, we may need to escape the reference to the postID column for certain DBMS. For example, if we are using PostgreSQL, we would have to write the condition as "postID"=:postID, because PostgreSQL by default will treat column names as case-insensitive.
We can also use $condition to specify more complex query conditions. Instead of a string, we let $condition be a CDbCriteria instance, which allows us to specify conditions other than the WHERE clause. For example,
$criteria=new CDbCriteria;
$criteria->select='title'; // only select the 'title' column
$criteria->condition='postID=:postID';
$criteria->params=array(':postID'=>10);
$post=Post::model()->find($criteria); // $params is not needed
Note, when using CDbCriteria as query condition, the $params parameter is no longer needed since it can be specified in CDbCriteria, as shown above.
An alternative way to CDbCriteria is passing an array to the find method. The array keys and values correspond to the criteria's property name and value, respectively. The above example can be rewritten as follows,
$post=Post::model()->find(array(
'select'=>'title',
'condition'=>'postID=:postID',
'params'=>array(':postID'=>10),
));
Info: When a query condition is about matching some columns with the specified values, we can use findByAttributes(). We let the $attributes parameters be an array of the values indexed by the column names. In some frameworks, this task can be achieved by calling methods like findByNameAndTitle. Although this approach looks attractive, it often causes confusion, conflict and issues like case-sensitivity of column names.
When multiple rows of data matching the specified query condition, we can bring them in all together using the following findAll methods, each of which has its counterpart find method, as we already described.
// find all rows satisfying the specified condition
$posts=Post::model()->findAll($condition,$params);
// find all rows with the specified primary keys
$posts=Post::model()->findAllByPk($postIDs,$condition,$params);
// find all rows with the specified attribute values
$posts=Post::model()->findAllByAttributes($attributes,$condition,$params);
// find all rows using the specified SQL statement
$posts=Post::model()->findAllBySql($sql,$params);
If nothing matches the query condition, findAll would return an empty array. This is different from find who would return null if nothing is found.
Besides the find and findAll methods described above, the following methods are also provided for convenience:
// get the number of rows satisfying the specified condition
$n=Post::model()->count($condition,$params);
// get the number of rows using the specified SQL statement
$n=Post::model()->countBySql($sql,$params);
// check if there is at least a row satisfying the specified condition
$exists=Post::model()->exists($condition,$params);
Updating Record
After an AR instance is populated with column values, we can change them and save them back to the database table.
$post=Post::model()->findByPk(10);
$post->title='new post title';
$post->save(); // save the change to database
As we can see, we use the same save() method to perform insertion and updating operations. If an AR instance is created using the new operator, calling save() would insert a new row into the database table; if the AR instance is the result of some find or findAll method call, calling save() would update the existing row in the table. In fact, we can use CActiveRecord::isNewRecord to tell if an AR instance is new or not.
It is also possible to update one or several rows in a database table without loading them first. AR provides the following convenient class-level methods for this purpose:
// update the rows matching the specified condition
Post::model()->updateAll($attributes,$condition,$params);
// update the rows matching the specified condition and primary key(s)
Post::model()->updateByPk($pk,$attributes,$condition,$params);
// update counter columns in the rows satisfying the specified conditions
Post::model()->updateCounters($counters,$condition,$params);
In the above, $attributes is an array of column values indexed by column names; $counters is an array of incremental values indexed by column names; and $condition and $params are as described in the previous subsection.
Deleting Record
We can also delete a row of data if an AR instance has been populated with this row.
$post=Post::model()->findByPk(10); // assuming there is a post whose ID is 10
$post->delete(); // delete the row from the database table
Note, after deletion, the AR instance remains unchanged, but the corresponding row in the database table is already gone.
The following class-level methods are provided to delete rows without the need of loading them first:
// delete the rows matching the specified condition
Post::model()->deleteAll($condition,$params);
// delete the rows matching the specified condition and primary key(s)
Post::model()->deleteByPk($pk,$condition,$params);
Data Validation
When inserting or updating a row, we often need to check if the column values comply to certain rules. This is especially important if the column values are provided by end users. In general, we should never trust anything coming from the client side.
AR performs data validation automatically when save() is being invoked. The validation is based on the rules specified by in the rules() method of the AR class. For more details about how to specify validation rules, refer to the Declaring Validation Rules section. Below is the typical workflow needed by saving a record:
if($post->save())
{
// data is valid and is successfully inserted/updated
}
else
{
// data is invalid. call getErrors() to retrieve error messages
}
When the data for inserting or updating is submitted by end users in an HTML form, we need to assign them to the corresponding AR properties. We can do so like the following:
$post->title=$_POST['title'];
$post->content=$_POST['content'];
$post->save();
If there are many columns, we would see a long list of such assignments. This can be alleviated by making use of the attributes property as shown below. More details can be found in the Securing Attribute Assignments section and the Creating Action section.
// assume $_POST['Post'] is an array of column values indexed by column names
$post->attributes=$_POST['Post'];
$post->save();
Comparing Records
Like table rows, AR instances are uniquely identified by their primary key values. Therefore, to compare two AR instances, we merely need to compare their primary key values, assuming they belong to the same AR class. A simpler way is to call CActiveRecord::equals(), however.
Info: Unlike AR implementation in other frameworks, Yii supports composite primary keys in its AR. A composite primary key consists of two or more columns. Correspondingly, the primary key value is represented as an array in Yii. The primaryKey property gives the primary key value of an AR instance.
Customization
CActiveRecord provides a few placeholder methods that can be overridden in child classes to customize its workflow.
· beforeValidate and afterValidate: these are invoked before and after validation is performed.
· beforeSave and afterSave: these are invoked before and after saving an AR instance.
· beforeDelete and afterDelete: these are invoked before and after an AR instance is deleted.
· afterConstruct: this is invoked for every AR instance created using the new operator.
· beforeFind: this is invoked before an AR finder is used to perform a query (e.g. find(), findAll()). This has been available since version 1.0.9.
· afterFind: this is invoked after every AR instance created as a result of query.
Using Transaction with AR
Every AR instance contains a property named dbConnection which is a CDbConnection instance. We thus can use the transaction feature provided by Yii DAO if it is desired when working with AR:
$model=Post::model();
$transaction=$model->dbConnection->beginTransaction();
try
{
// find and save are two steps which may be intervened by another request
// we therefore use a transaction to ensure consistency and integrity
$post=$model->findByPk(10);
$post->title='new post title';
$post->save();
$transaction->commit();
}
catch(Exception $e)
{
$transaction->rollBack();
}
Named Scopes
Note: The support for named scopes has been available since version 1.0.5. The original idea of named scopes came from Ruby on Rails.
A named scope represents a named query criteria that can be combined with other named scopes and applied to an active record query.
Named scopes are mainly declared in the CActiveRecord::scopes() method as name-criteria pairs. The following code declares two named scopes, published and recently, in the Post model class:
class Post extends CActiveRecord
{
......
public function scopes()
{
return array(
'published'=>array(
'condition'=>'status=1',
),
'recently'=>array(
'order'=>'createTime DESC',
'limit'=>5,
),
);
}
}
Each named scope is declared as an array which can be used to initialize a CDbCriteria instance. For example, the recently named scope specifies that the order property to be createTime DESC and the limit property to be 5, which translates to a query criteria that should bring back the most recent 5 posts.
Named scopes are mostly used as modifiers to the find method calls. Several named scopes may be chained together and result in a more restrictive query result set. For example, to find the recently published posts, we can use the following code:
$posts=Post::model()->published()->recently()->findAll();
In general, named scopes must appear to the left of a find method call. Each of them provides a query criteria, which is combined with other criterias, including the one passed to the find method call. The net effect is like adding a list of filters to a query.
Starting from version 1.0.6, named scopes can also be used with update and delete methods. For example, the following code would delete all recently published posts:
Post::model()->published()->recently()->delete();
Note: Named scopes can only be used with class-level methods. That is, the method must be called using ClassName::model().
Parameterized Named Scopes
Named scopes can be parameterized. For example, we may want to customize the number of posts specified by the recently named scope. To do so, instead of declaring the named scope in the CActiveRecord::scopes method, we need to define a new method whose name is the same as the scope name:
public function recently($limit=5)
{
$this->getDbCriteria()->mergeWith(array(
'order'=>'createTime DESC',
'limit'=>$limit,
));
return $this;
}
Then, we can use the following statement to retrieve the 3 recently published posts:
$posts=Post::model()->published()->recently(3)->findAll();
If we do not supply the parameter 3 in the above, we would retrieve the 5 recently published posts by default.
Default Named Scope
A model class can have a default named scope that would be applied for all queries (including relational ones) about the model. For example, a website supporting multiple languages may only want to display contents that are in the language the current user specifies. Because there may be many queries about the site contents, we can define a default named scope to solve this problem. To do so, we override the CActiveRecord::defaultScope method as follows,
class Content extends CActiveRecord
{
public function defaultScope()
{
return array(
'condition'=>"language='".Yii::app()->language."'",
);
}
}
Now, if the following method call will automatically use the query criteria as defined above:
$contents=Content::model()->findAll();
Note that default named scope only applies to SELECT queries. It is ignored for INSERT, UPDATE and DELETE queries.
2010年4月27日 1时30分12秒 albert
Relational Active Record
我们已经知道如何通过Active Record（AR）从单个数据表中取得数据了，在这一节中，我们将要介绍如何使用AR来连接关联的数据表获取数据。
在使用关联AR之前，首先要在数据库中建立关联的数据表之间的主键-外键关联，AR需要通过分析数据库中的定义数据表关联的元信息，来决定如何连接数据。
注意: 从1.0.1版往后，使用关联AR不再依赖数据库中的外键约束定义。
在这一节中，我们将以下面这个简单的实体-关系(ER)图所描述的数据库为例，来介绍如何使用包含关联的ActiveRecord。
ER Diagram
说明: 不同的关系数据库对外键约束的支持有所不同.
SQLite是不支持外键约束的，但允许你在建立数据表时定义外键约束，AR会利用 DDL声明中的约束定义获得相应的信息，用来支持关联查询。
MySQL数据库中的InnoDB表引擎支持外键约束，而MyISAM引擎不支持。因此我们建议你使用 InnoDB作为数据库的表引擎。当然你也可以使用MyISAM，可以通过下面的一个小技巧来实现 关联查询。
~ sql CREATE TABLE Foo ( id INTEGER NOT NULL PRIMARY KEY ); CREATE TABLE bar ( id INTEGER NOT NULL PRIMARY KEY, fooID INTEGER COMMENT 'CONSTRAINT FOREIGN KEY (fooID) REFERENCES Foo(id)' ); ~ 就像上面的例子中的做法，把外键约束的定义写在字段注释中，AR可以识别这些信息来确定 数据表之间的关联。
如何声明关联
在使用AR进行关联查询之前，我们需要告诉AR各个AR类之间有怎样的关联。
AR类之间的关联直接反映着数据库中这个类所代表的数据表之间的关联。从关系数据库的角度来说，两个数据表A，B之间可能的关联有三种：一对多（例如User和Post），一对一（例如User和Profile），多对多（例如Category和Post）。而在AR中，关联有以下四种：
· BELONGS_TO: 如果数据表A和B的关系是一对多，那我们就说B属于A（B belongs to A），例如Post属于User。
· HAS_MANY: 如果数据表A和B的关系是多对一，那我们就说B有多个A（B has many A），例如User有多个Post。
· HAS_ONE: 这是‘HAS_MANY’关系中的一个特例，当A最多有一个的时候，我们说B有一个A （B has one A），例如一个User就只有一个Profile
· MANY_MANY: 这个相当于关系数据库中的多对多关系。因为绝大多数关系数据库并不直接支持多对多的关系，这时通常都需要一个单独的关联表，把多对多的关系分解为两个一对多的关系。在我们的例子中，PostCategory就是这个用作关联的表。用AR的方式去理解的话，我们可以认为 MANY_MANY关系是由BELONGS_TO和HAS_MANY组成的. 例如Post属于多个Category并且 Category有多个Post。
在AR中声明关联，是通过覆盖（Override）父类CActiveRecord中的relations() 方法来实现的。这个方法返回一个包含了关系定义的数组，数组中的每一组键值代表一个关联：
~ php 'VarName'=>array('RelationType', 'ClassName', 'ForeignKey', ...additional options) ~
这里的VarName是这个关联的名称；RelationType指定了这个关联的类型，有四个常量代表了四种关联的类型：self::BELONGS_TO，self::HAS_ONE，self::HAS_MANY和self::MANY_MANY； ClassName是这个关系关联到的AR类的类名；ForeignKey指定了这个关联是通过哪个外键联系起来的。后面的additional options可以加入一些额外的设置，后面会做介绍。
下面的代码演示了如何定义User和Post之间的关联。
~ php class Post extends CActiveRecord { public function relations() { return array( 'author'=>array(self::BELONGS_TO, 'User', 'authorID'), 'categories'=>array(self::MANY_MANY, 'Category', 'PostCategory(postID, categoryID)'), ); } }
class User extends CActiveRecord { public function relations() { return array( 'posts'=>array(self::HAS_MANY, 'Post', 'authorID'), 'profile'=>array(self::HAS_ONE, 'Profile', 'ownerID'), ); } } ~
说明: 有时外键可能由两个或更多字段组成，在这里可以将多个字段名由逗号或空格分隔， 一并写在这里。对于多对多的关系，关联表必须在外键中注明，例如在Post类的categories 关联中，外键就需要写成PostCategory(postID, categoryID)。
在AR类中声明关联时，每个关联会作为一个属性添加到AR类中，属性名就是关联的名称。在进行关联查询时，这些属性就会被设置为关联到的AR类的实例，例如在查询取得一个Post实例时，它的$author属性就是代表Post作者的一个User类的实例。
关联查询
进行关联查询最简单的方式就是访问一个关联AR对象的某个关联属性。如果这个属性之前没有被访问过，这时就会启动一个关联查询，通过当前AR对象的主键连接相关的表，来取得关联对象的值，然后将这些数据保存在对象的属性中。这种方式叫做“延迟加载”，也就是只有等到访问到某个属性时，才会真正到数据库中把这些关联的数据取出来。下面的例子描述了延迟加载的过程：
~ php // retrieve the post whose ID is 10 $post=Post::model()->findByPk(10); // retrieve the post's author: a relational query will be performed here $author=$post->author; ~
说明: If there is no related instance for a relationship, the corresponding property could be either null or an empty array. For BELONGS_TO and HAS_ONE relationships, the result is null; for HAS_MANY and MANY_MANY, it is an empty array.
The lazy loading approach is very convenient to use, but it is not efficient in some scenarios. For example, if we want to access the author information for N posts, using the lazy approach would involve executing N join queries. We should resort to the so-called eager loading approach under this circumstance.
The eager loading approach retrieves the related AR instances together with the main AR instance(s). This is accomplished by using the with() method together with one of the find or findAll methods in AR. For example,
~ php $posts=Post::model()->with('author')->findAll(); ~
The above code will return an array of Post instances. Unlike the lazy approach, the author property in each Post instance is already populated with the related User instance before we access the property. Instead of executing a join query for each post, the eager loading approach brings back all posts together with their authors in a single join query!
We can specify multiple relationship names in the with() method and the eager loading approach will bring them back all in one shot. For example, the following code will bring back posts together with their authors and categories:
~ php $posts=Post::model()->with('author','categories')->findAll(); ~
We can also do nested eager loading. Instead of a list of relationship names, we pass in a hierarchical representation of relationship names to the with() method, like the following,
~ php $posts=Post::model()->with(array( 'author'=>array( 'profile', 'posts'), 'categories'))->findAll(); ~
The above example will bring back all posts together with their author and categories. It will also bring back each author's profile and posts.
说明: The AR implementation in Yii is very efficient. When eager loading a hierarchy of related objects involving N HAS_MANY or MANY_MANY relationships, it will take N+1 SQL queries to obtain the needed results. This means it needs to execute 3 SQL queries in the last example because of the posts and categories properties. Other frameworks take a more radical approach by using only one SQL query. At first look, this approach seems more efficient because fewer queries are being parsed and executed by DBMS. It is in fact impractical in reality for two reasons. First, there are many repetitive data columns in the result which takes extra time to transmit and process. Second, the number of rows in the result set grows exponentially with the number of tables involved, which makes it simply unmanageable as more relationships are involved.
Relational Query Options
We mentioned that additional options can be specified in relationship declaration. These options, specified as name-value pairs, are used to customize the relational query. They are summarized as below.
· select: a list of columns to be selected for the related AR class. It defaults to '*', meaning all columns. Column names should be disambiguated using aliasToken if they appear in an expression (e.g. COUNT(??.name) AS nameCount).
· condition: the WHERE clause. It defaults to empty. Note, column references need to be disambiguated using aliasToken (e.g. ??.id=10).
· order: the ORDER BY clause. It defaults to empty. Note, column references need to be disambiguated using aliasToken (e.g. ??.age DESC).
· with: a list of child related objects that should be loaded together with this object. Note, this is only honored by lazy loading, not eager loading.
· joinType: type of join for this relationship. It defaults to LEFT OUTER JOIN.
· aliasToken: the column prefix placeholder. It will be replaced by the corresponding table alias to disambiguate column references. It defaults to '??.'.
· alias: the alias for the table associated with this relationship. This option has been available since version 1.0.1. It defaults to null, meaning the table alias is automatically generated. This is different from aliasToken in that the latter is just a placeholder and will be replaced by the actual table alias.
In addition, the following options are available for certain relationships during lazy loading:
· group: the GROUP BY clause. It defaults to empty. Note, column references need to be disambiguated using aliasToken (e.g. ??.age). This option only applies to HAS_MANY and MANY_MANY relationships.
· having: the HAVING clause. It defaults to empty. Note, column references need to be disambiguated using aliasToken (e.g. ??.age). This option only applies to HAS_MANY and MANY_MANY relationships. Note: option has been available since version 1.0.1.
· limit: limit of the rows to be selected. This option does NOT apply to BELONGS_TO relation.
· offset: offset of the rows to be selected. This option does NOT apply to BELONGS_TO relation.
Below we modify the posts relationship declaration in the User by including some of the above options:
~ php class User extends CActiveRecord { public function relations() { return array( 'posts'=>array(self::HAS_MANY, 'Post', 'authorID' 'order'=>'??.createTime DESC', 'with'=>'categories'), 'profile'=>array(self::HAS_ONE, 'Profile', 'ownerID'), ); } } ~
Now if we access $author->posts, we would obtain the author's posts sorted according to their creation time in descending order. Each post instance also has its categories loaded.
说明: When a column name appears in two or more tables being joined together, it needs to be disambiguated. This is done by prefixing the column name with its table name. For example, id becomes Team.id. In AR relational queries, however, we do not have this freedom because the SQL statements are automatically generated by AR which systematically gives each table an alias. Therefore, in order to avoid column name conflict, we use a placeholder to indicate the existence of a column which needs to be disambiguated. AR will replace the placeholder with a suitable table alias and properly disambiguate the column.
延伸阅读:
Yii是什么