本文针对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,'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,'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函数，这个函数十分关键，用于定义表之间的关系，下面我将详细说明其中含义

array(self::BELONGS_TO,'relation_type_id') 这句代码中结构如下 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表中是双主键可以采用下列方式实现，从软件工程的角度不推荐这样的做法，每个表最好使用独立无意义主键，不然容易出现各种问题，而且不方便管理

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',);
}

/**

@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',);
}

/**

@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,));

}
}

用户表：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',array('name,password,salt,email','max'=>255),array('lastlogin','safe'),// The following rule is used by search().
// Please remove those attributes that should not be searched.
array('id,name,lastlogin,email,status',);
}

/**

@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,'anotherUser_name' => array(self::HAS_MANY,);
}

/**

@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,));

}
}

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('@'),// 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',));

}

/**

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',));

}

/**

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 访问方式，就是那些用户能够访问到这个模块

PHP;"> array('allow',

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

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

actionXXX 各个action函数

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

user->id != null){ $dataProvider=new CActiveDataProvider( 'SocialRelation',)); }

}

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

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

performAjaxValidation 用于Ajax验证。

5、视图View

index.PHP

PHP;"> $this->breadcrumbs=array(
'Social Relations',);
?>

Social Relations

<?php $this->widget('zii.widgets.CListView',array(
'dataProvider'=>$dataProvider,'itemView'=>'_view',)); ?>

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

_view.PHP

PHP;">

<?php echo CHtml::encode($data->getAttributeLabel('relation_id')); ?>:
<?php echo CHtml::link(CHtml::encode($data->relation_id),array('view','id'=>$data->relation_id)); ?>

<?php echo CHtml::encode($data->getAttributeLabel('relation_type_id')); ?>:
<?php echo CHtml::encode($data->relation_type_id); ?>

<?php echo CHtml::encode($data->getAttributeLabel('relation_type_name')); ?>:
<?php
echo $data->relationType->relation_type_name;
?>

<?php echo CHtml::encode($data->getAttributeLabel('user_id')); ?>:
<?php echo CHtml::encode($data->user_id); ?>

<?php echo CHtml::encode($data->getAttributeLabel('user_name')); ?>:
<?php
echo $data->user->name;
?>

<?php echo CHtml::encode($data->getAttributeLabel('another_user_id')); ?>:
<?php echo CHtml::encode($data->another_user_id); ?>

<?php echo CHtml::encode($data->getAttributeLabel('another_user_name')); ?>:
<?php
echo $data->anotherUser->name;
?>

6、总结

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

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

Yii多表联合查询操作详解

1、多表联查实现方法

2、整体框架

3、CActiveRecord

关系类型表：SocialRelationType.PHP

用户表：AccessUser.PHP

4、Controller

5、视图View

Social Relations

6、总结

相关文章

Yii多表联合查询操作详解

1、多表联查实现方法

2、 整体框架

3、CActiveRecord

关系类型表：SocialRelationType.PHP

用户表：AccessUser.PHP

4、Controller

5、视图View

Social Relations

6、总结

相关文章

2、整体框架