Yii多表联合查询操作详解

时间：2022-08-30 07:26:08

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

1、多表联查实现方法

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

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

2、 整体框架

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

3、CActiveRecord

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

SocialRelation.php

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
<?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表中是双主键可以采用下列方式实现，从软件工程的角度不推荐这样的做法，每个表最好使用独立无意义主键，不然容易出现各种问题，而且不方便管理

?
1
2
'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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
<?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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
<?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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
<?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 访问方式，就是那些用户能够访问到这个模块

?
1
2
3
4
array('allow', // allow all users to perform 'index' and 'view' actions
        'actions'=>array('index','view'),
        'users'=>array('*'),
      ),

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

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

actionXXX 各个action函数

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<?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、总结

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

Yii多表联合查询操作详解

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

标签：

相关文章