本文针对Yii多表联查进行汇总描述，供大家参考，具体内容如下

1、多表联查实现方法

有两种方式一种使用DAO写SQL语句实现，这种实现理解起来相对轻松，只要保证SQL语句不写错就行了。缺点也很明显，比较零散，而且不符合YII的推荐框架，最重要的缺点在于容易写错。

还有一种便是下面要说的使用YII自带的CActiveRecord实现多表联查

2、 整体框架

我们需要找到一个用户的好友关系，用户的信息放在用户表中，用户之间的关系放在关系表中，而关系的内容则放在关系类型表中。明显的我们只需要以关系表为主表联查其他两个表即可。我主要从代码的角度，分析下实现的过程。

3、CActiveRecord

我们首先需要对3张表建立相应的model，下面是关系表的代码

SocialRelation.php

<?php 
 
/** 
 * This is the model class for table "{{social_relation}}". 
 * 
 * The followings are the available columns in table "{{social_relation}}": 
 * @property integer $relation_id 
 * @property integer $relation_type_id 
 * @property integer $user_id 
 * @property integer $another_user_id 
 * 
 * The followings are the available model relations: 
 * @property SocialRelationType $relationType 
 * @property AccessUser $user 
 * @property AccessUser $anotherUser 
 */ 
class SocialRelation extends CActiveRecord 
{ 
  /** 
   * Returns the static model of the specified AR class. 
   * @param string $className active record class name. 
   * @return SocialRelation the static model class 
   */ 
  public static function model($className=__CLASS__) 
  { 
    return parent::model($className); 
  } 
 
  /** 
   * @return string the associated database table name 
   */ 
  public function tableName() 
  { 
    return "{{social_relation}}"; 
  } 
 
  /** 
   * @return array validation rules for model attributes. 
   */ 
  public function rules() 
  { 
    // NOTE: you should only define rules for those attributes that 
    // will receive user inputs. 
    return array( 
      array("relation_type_id, user_id, another_user_id", "numerical", "integerOnly"=>true), 
      // The following rule is used by search(). 
      // Please remove those attributes that should not be searched. 
      array("relation_id, relation_type_id, user_id, another_user_id", "safe", "on"=>"search"), 
    ); 
  } 
 
  /** 
   * @return array relational rules. 
   */ 
  public function relations() 
  { 
    // NOTE: you may need to adjust the relation name and the related 
    // class name for the relations automatically generated below. 
    return array( 
      "relationType" => array(self::BELONGS_TO, "SocialRelationType", "relation_type_id"), 
      "user" => array(self::BELONGS_TO, "AccessUser", "user_id"), 
      "anotherUser" => array(self::BELONGS_TO, "AccessUser", "another_user_id"), 
    ); 
  } 
 
  /** 
   * @return array customized attribute labels (name=>label) 
   */ 
  public function attributeLabels() 
  { 
    return array( 
      "relation_id" => "Relation", 
      "relation_type_id" => "Relation Type", 
      "relation_type_name" => "Relation Name", 
      "user_id" => "User ID", 
      "user_name" => "User Name", 
      "another_user_id" => "Another User", 
      "another_user_name" => "Another User Name", 
    ); 
  } 
 
  /** 
   * Retrieves a list of models based on the current search/filter conditions. 
   * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions. 
   */ 
  public function search() 
  { 
    // Warning: Please modify the following code to remove attributes that 
    // should not be searched. 
 
    $criteria=new CDbCriteria; 
 
    $criteria->compare("relation_id",$this->relation_id); 
    $criteria->compare("relation_type_id",$this->relation_type_id); 
    $criteria->compare("user_id",$this->user_id); 
    $criteria->compare("another_user_id",$this->another_user_id); 
    $criteria->with=array( 
      "relationType", 
    ); 
 
    return new CActiveDataProvider($this, array( 
      "criteria"=>$criteria, 
    )); 
  } 
} 

为了描述方便我们约定 主表为A表(执行查询的那个表)， 引用表为B表(外键所引用的表)
建议使用Gii自动生成模型，这样能够节省大量时间，为了测试方便，可以对主表生成CRUD，就是增删改查页面，其他的引用表只用生成model就行了。
1. model函数、tablename函数用于得到这个模型和得到数据库表基本信息。自动生成无需修改

2.rules函数，这个函数主要用于规定参数检验方式，注意即使有些参数不需要校验，也必须出现在rules中。不然模型将无法得到参数

3.relation函数，这个函数十分关键，用于定义表之间的关系，下面我将详细说明其中含义

"relationType" => array(self::BELONGS_TO, "SocialRelationType", "relation_type_id")  
 这句代码中结构如下
"VarName"=>array("RelationType", "ClassName", "ForeignKey", ...additional options)
VarName 是关系的名字，我们以后会用这个名字访问外键引用表的字段

RelationType是关系的类型，十分重要，如果设定错误会导致一些奇怪而且难以检查的错误，Yii一共提供了4种关系

BELONGS_TO（属于）: 如果表 A 和 B 之间的关系是一对多，则 表 B 属于 表 A
HAS_MANY（有多个）: 如果表 A 和 B 之间的关系是一对多，则 A 有多个 B
HAS_ONE（有一个）: 这是 HAS_MANY 的一个特例，A 最多有一个 B
MANY_MANY: 这个对应于数据库中的 多对多关系
ClassName是引用表名，就是外键所引用的表的名字，也就是B表表名

ForeignKey是外键名，主要这里填写的是外键在主表中的名字，也就是外键在A表中的表名，切记不要填错了

如果B表中是双主键可以采用下列方式实现，从软件工程的角度不推荐这样的做法，每个表最好使用独立无意义主键，不然容易出现各种问题，而且不方便管理

"categories"=>array(self::MANY_MANY, "Category", 
        "tbl_post_category(post_id, category_id)"), 

 additional option 附加选项，很少用到
4 attributeLabels函数，这就是表属性的显示名称了，有点点像powerdesigner中code和name的关系前面一部分为数据库字段名，后面一部分为显示名称
5 search函数，用于生成表查询结果的函数，可以在此加一些限制条件，具体的使用方法就不在这里说明了，可以参考API中CDbCriteria的讲解。如果使用Gii生成那么不需要怎么修改。

同理我们生成，剩下的两个引用表

关系类型表：SocialRelationType.php

<?php 
 
/** 
 * This is the model class for table "{{social_relation_type}}". 
 * 
 * The followings are the available columns in table "{{social_relation_type}}": 
 * @property integer $relation_type_id 
 * @property string $relation_type_name 
 * 
 * The followings are the available model relations: 
 * @property SocialRelation[] $socialRelations 
 */ 
class SocialRelationType extends CActiveRecord 
{ 
  /** 
   * Returns the static model of the specified AR class. 
   * @param string $className active record class name. 
   * @return SocialRelationType the static model class 
   */ 
  public static function model($className=__CLASS__) 
  { 
    return parent::model($className); 
  } 
 
  /** 
   * @return string the associated database table name 
   */ 
  public function tableName() 
  { 
    return "{{social_relation_type}}"; 
  } 
 
  /** 
   * @return array validation rules for model attributes. 
   */ 
  public function rules() 
  { 
    // NOTE: you should only define rules for those attributes that 
    // will receive user inputs. 
    return array( 
      array("relation_type_name", "length", "max"=>10), 
      // The following rule is used by search(). 
      // Please remove those attributes that should not be searched. 
      array("relation_type_id, relation_type_name", "safe", "on"=>"search"), 
    ); 
  } 
 
  /** 
   * @return array relational rules. 
   */ 
  public function relations() 
  { 
    // NOTE: you may need to adjust the relation name and the related 
    // class name for the relations automatically generated below. 
    return array( 
      "socialRelations" => array(self::HAS_MANY, "SocialRelation", "relation_type_id"), 
    ); 
  } 
 
  /** 
   * @return array customized attribute labels (name=>label) 
   */ 
  public function attributeLabels() 
  { 
    return array( 
      "relation_type_id" => "Relation Type", 
      "relation_type_name" => "Relation Type Name", 
    ); 
  } 
 
  /** 
   * Retrieves a list of models based on the current search/filter conditions. 
   * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions. 
   */ 
  public function search() 
  { 
    // Warning: Please modify the following code to remove attributes that 
    // should not be searched. 
 
    $criteria=new CDbCriteria; 
 
    $criteria->compare("relation_type_id",$this->relation_type_id); 
    $criteria->compare("relation_type_name",$this->relation_type_name,true); 
 
    return new CActiveDataProvider($this, array( 
      "criteria"=>$criteria, 
    )); 
  } 
} 

用户表：AccessUser.php

<?php 
 
/** 
 * This is the model class for table "{{access_user}}". 
 * 
 * The followings are the available columns in table "{{access_user}}": 
 * @property integer $id 
 * @property string $name 
 * @property string $password 
 * @property string $lastlogin 
 * @property string $salt 
 * @property string $email 
 * @property integer $status 
 * 
 * The followings are the available model relations: 
 * @property SocialRelation[] $socialRelations 
 * @property SocialRelation[] $socialRelations1 
 */ 
class AccessUser extends CActiveRecord 
{ 
  /** 
   * Returns the static model of the specified AR class. 
   * @param string $className active record class name. 
   * @return AccessUser the static model class 
   */ 
  public static function model($className=__CLASS__) 
  { 
    return parent::model($className); 
  } 
 
  /** 
   * @return string the associated database table name 
   */ 
  public function tableName() 
  { 
    return "{{access_user}}"; 
  } 
 
  /** 
   * @return array validation rules for model attributes. 
   */ 
  public function rules() 
  { 
    // NOTE: you should only define rules for those attributes that 
    // will receive user inputs. 
    return array( 
      array("status", "numerical", "integerOnly"=>true), 
      array("name, password, salt, email", "length", "max"=>255), 
      array("lastlogin", "safe"), 
      // The following rule is used by search(). 
      // Please remove those attributes that should not be searched. 
      array("id, name, password, lastlogin, salt, email, status", "safe", "on"=>"search"), 
    ); 
  } 
 
  /** 
   * @return array relational rules. 
   */ 
  public function relations() 
  { 
    // NOTE: you may need to adjust the relation name and the related 
    // class name for the relations automatically generated below. 
    return array( 
      "user_name" => array(self::HAS_MANY, "SocialRelation", "user_id"), 
      "anotherUser_name" => array(self::HAS_MANY, "SocialRelation", "another_user_id"), 
    ); 
  } 
 
  /** 
   * @return array customized attribute labels (name=>label) 
   */ 
  public function attributeLabels() 
  { 
    return array( 
      "id" => "ID", 
      "name" => "Name", 
      "password" => "Password", 
      "lastlogin" => "Lastlogin", 
      "salt" => "Salt", 
      "email" => "Email", 
      "status" => "Status", 
    ); 
  } 
 
  /** 
   * Retrieves a list of models based on the current search/filter conditions. 
   * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions. 
   */ 
  public function search() 
  { 
    // Warning: Please modify the following code to remove attributes that 
    // should not be searched. 
 
    $criteria=new CDbCriteria; 
 
    $criteria->compare("id",$this->id); 
    $criteria->compare("name",$this->name,true); 
    $criteria->compare("password",$this->password,true); 
    $criteria->compare("lastlogin",$this->lastlogin,true); 
    $criteria->compare("salt",$this->salt,true); 
    $criteria->compare("email",$this->email,true); 
    $criteria->compare("status",$this->status); 
 
    return new CActiveDataProvider($this, array( 
      "criteria"=>$criteria, 
    )); 
  } 
} 

4、Controller
三张表介绍完了后，下面就应当介绍Controller了，同样的我们使用Gii生成主表（A表）的CRUD后就能得到controller，我们只需要对其进行一些修改即可，代码如下

SocialRelationController.php

<?php 
 
class SocialRelationController extends Controller 
{ 
  /** 
   * @var string the default layout for the views. Defaults to "//layouts/column2", meaning 
   * using two-column layout. See "protected/views/layouts/column2.php". 
   */ 
  public $layout="//layouts/column2"; 
 
  /** 
   * @return array action filters 
   */ 
  public function filters() 
  { 
    return array( 
      "accessControl", // perform access control for CRUD operations 
      "postOnly + delete", // we only allow deletion via POST request 
    ); 
  } 
 
  /** 
   * Specifies the access control rules. 
   * This method is used by the "accessControl" filter. 
   * @return array access control rules 
   */ 
  public function accessRules() 
  { 
    return array( 
      array("allow", // allow all users to perform "index" and "view" actions 
        "actions"=>array("index","view"), 
        "users"=>array("*"), 
      ), 
      array("allow", // allow authenticated user to perform "create" and "update" actions 
        "actions"=>array("create","update"), 
        "users"=>array("@"), 
      ), 
      array("allow", // allow admin user to perform "admin" and "delete" actions 
        "actions"=>array("admin","delete"), 
        "users"=>array("admin"), 
      ), 
      array("deny", // deny all users 
        "users"=>array("*"), 
      ), 
    ); 
  } 
 
  /** 
   * Displays a particular model. 
   * @param integer $id the ID of the model to be displayed 
   */ 
  public function actionView($id) 
  { 
    $this->render("view",array( 
      "model"=>$this->loadModel($id), 
    )); 
  } 
 
  /** 
   * Creates a new model. 
   * If creation is successful, the browser will be redirected to the "view" page. 
   */ 
  public function actionCreate() 
  { 
    $model=new SocialRelation; 
 
    // Uncomment the following line if AJAX validation is needed 
    // $this->performAjaxValidation($model); 
 
    if(isset($_POST["SocialRelation"])) 
    { 
      $model->attributes=$_POST["SocialRelation"]; 
      if($model->save()) 
        $this->redirect(array("view","id"=>$model->relation_id)); 
    } 
 
    $this->render("create",array( 
      "model"=>$model, 
    )); 
  } 
 
  /** 
   * Updates a particular model. 
   * If update is successful, the browser will be redirected to the "view" page. 
   * @param integer $id the ID of the model to be updated 
   */ 
  public function actionUpdate($id) 
  { 
    $model=$this->loadModel($id); 
 
    // Uncomment the following line if AJAX validation is needed 
    // $this->performAjaxValidation($model); 
 
    if(isset($_POST["SocialRelation"])) 
    { 
      $model->attributes=$_POST["SocialRelation"]; 
      if($model->save()) 
        $this->redirect(array("view","id"=>$model->relation_id)); 
    } 
 
    $this->render("update",array( 
      "model"=>$model, 
    )); 
  } 
 
  /** 
   * Deletes a particular model. 
   * If deletion is successful, the browser will be redirected to the "admin" page. 
   * @param integer $id the ID of the model to be deleted 
   */ 
  public function actionDelete($id) 
  { 
    $this->loadModel($id)->delete(); 
 
    // if AJAX request (triggered by deletion via admin grid view), we should not redirect the browser 
    if(!isset($_GET["ajax"])) 
      $this->redirect(isset($_POST["returnUrl"]) ? $_POST["returnUrl"] : array("admin")); 
  } 
 
  /** 
   * Lists all models. 
   */ 
  public function actionIndex() 
  { 
    if(Yii::app()->user->id != null){ 
      $dataProvider=new CActiveDataProvider( 
        "SocialRelation",  
        array("criteria"=>array("condition"=>"user_id=".Yii::app()->user->id, 
      )) 
      ); 
      $this->render("index",array( 
        "dataProvider"=>$dataProvider, 
      )); 
    } 
     
  } 
 
  /** 
   * Manages all models. 
   */ 
  public function actionAdmin() 
  { 
    $model=new SocialRelation("search"); 
    $model->unsetAttributes(); // clear any default values 
    if(isset($_GET["SocialRelation"])) 
      $model->attributes=$_GET["SocialRelation"]; 
 
    $this->render("admin",array( 
      "model"=>$model, 
    )); 
  } 
 
  /** 
   * Returns the data model based on the primary key given in the GET variable. 
   * If the data model is not found, an HTTP exception will be raised. 
   * @param integer $id the ID of the model to be loaded 
   * @return SocialRelation the loaded model 
   * @throws CHttpException 
   */ 
  public function loadModel($id) 
  { 
    $model=SocialRelation::model()->findByPk($id); 
    if($model===null) 
      throw new CHttpException(404,"The requested page does not exist."); 
    return $model; 
  } 
 
  /** 
   * Performs the AJAX validation. 
   * @param SocialRelation $model the model to be validated 
   */ 
  protected function performAjaxValidation($model) 
  { 
    if(isset($_POST["ajax"]) && $_POST["ajax"]==="social-relation-form") 
    { 
      echo CActiveForm::validate($model); 
      Yii::app()->end(); 
    } 
  } 
} 

简单介绍下其中各个函数和变量
$layout 就是布局文件的位置了，布局文件如何使用，这里不做讨论

filters 定义过滤器，这里面水很深

accessRules 访问方式，就是那些用户能够访问到这个模块

array("allow", // allow all users to perform "index" and "view" actions 
        "actions"=>array("index","view"), 
        "users"=>array("*"), 
      ), 

allow 表示允许访问的规则如下，deny表示拒绝访问的规则如下。
action表示规定规则使用的动作

user表示规则适用的用户群组，*表示所有用户,@表示登录后的用户,admin表示管理员用户

actionXXX 各个action函数

这里值得注意的是 这个函数

public function actionIndex() 
  { 
    if(Yii::app()->user->id != null){ 
      $dataProvider=new CActiveDataProvider( 
        "SocialRelation",  
        array("criteria"=>array("condition"=>"user_id=".Yii::app()->user->id, 
      )) 
      ); 
      $this->render("index",array( 
        "dataProvider"=>$dataProvider, 
      )); 
    } 
     
  } 

其中我们可以在dataProvider中设置相应的查询条件，注意这里设置是对于主表(A表)进行的，用的字段名也是主表中的，因为我们要显示的是当前用户的好友，于是，这里我们使用Yii::app()->user->id取得当前用户的id 。

loadModel 用于装载模型，这里我们可以看到findByPk查询了数据库。

performAjaxValidation 用于Ajax验证。

5、视图View

index.php

<?php 
/* @var $this SocialRelationController */ 
/* @var $dataProvider CActiveDataProvider */ 
 
$this->breadcrumbs=array( 
  "Social Relations", 
); 
?> 
 
<h1>Social Relations</h1> 
 
<?php $this->widget("zii.widgets.CListView", array( 
  "dataProvider"=>$dataProvider, 
  "itemView"=>"_view", 
)); ?> 

我们使用一个 CListView控件进行显示，其中itemView为内容显示的具体表单，dataProvider这个是内容源，我们在controller中已经设定了。

_view.php

<?php 
/* @var $this SocialRelationController */ 
/* @var $data SocialRelation */ 
?> 
 
<div class="view"> 
 
  <b><?php echo CHtml::encode($data->getAttributeLabel("relation_id")); ?>:</b> 
  <?php echo CHtml::link(CHtml::encode($data->relation_id), array("view", "id"=>$data->relation_id)); ?> 
  <br /> 
 
  <b><?php echo CHtml::encode($data->getAttributeLabel("relation_type_id")); ?>:</b> 
  <?php echo CHtml::encode($data->relation_type_id); ?> 
  <br /> 
 
  <b><?php echo CHtml::encode($data->getAttributeLabel("relation_type_name")); ?>:</b> 
  <?php  
    echo $data->relationType->relation_type_name; 
  ?> 
  <br /> 
   
  <b><?php echo CHtml::encode($data->getAttributeLabel("user_id")); ?>:</b> 
  <?php echo CHtml::encode($data->user_id); ?> 
  <br /> 
 
  <b><?php echo CHtml::encode($data->getAttributeLabel("user_name")); ?>:</b> 
  <?php  
    echo $data->user->name; 
  ?> 
  <br /> 
 
  <b><?php echo CHtml::encode($data->getAttributeLabel("another_user_id")); ?>:</b> 
  <?php echo CHtml::encode($data->another_user_id); ?> 
  <br /> 
 
  <b><?php echo CHtml::encode($data->getAttributeLabel("another_user_name")); ?>:</b> 
  <?php 
    echo $data->anotherUser->name; 
  ?> 
  <br /> 
   
</div> 

主要都是类似的，我们看其中的一条
复制代码 代码如下:<b><?php echo CHtml::encode($data->getAttributeLabel("relation_type_name")); ?>:</b> 
<?php echo $data->relationType->relation_type_name; ?> 
第一行为显示标签，在模型中我们设定的显示名就在这里体现出来
第二行为内容显示，这里的relationType是在模型中设置的关系名字，后面的relation_type_name是引用表的字段名(B表中的名字)

6、总结

通过上面的步骤，我们就实现了整个联合查询功能，效果图如下所示：

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持网页设计。