Yii多表联合查询操作详解

本文针对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、总结

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

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