您好,登錄后才能下訂單哦!
Yii中怎么實(shí)現(xiàn)多表聯(lián)合查詢操作,針對(duì)這個(gè)問(wèn)題,這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答,希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。
1、多表聯(lián)查實(shí)現(xiàn)方法
有兩種方式一種使用DAO寫(xiě)SQL語(yǔ)句實(shí)現(xiàn),這種實(shí)現(xiàn)理解起來(lái)相對(duì)輕松,只要保證SQL語(yǔ)句不寫(xiě)錯(cuò)就行了。缺點(diǎn)也很明顯,比較零散,而且不符合YII的推薦框架,最重要的缺點(diǎn)在于容易寫(xiě)錯(cuò)。
還有一種便是下面要說(shuō)的使用YII自帶的CActiveRecord實(shí)現(xiàn)多表聯(lián)查
2、 整體框架
我們需要找到一個(gè)用戶的好友關(guān)系,用戶的信息放在用戶表中,用戶之間的關(guān)系放在關(guān)系表中,而關(guān)系的內(nèi)容則放在關(guān)系類型表中。明顯的我們只需要以關(guān)系表為主表聯(lián)查其他兩個(gè)表即可。我主要從代碼的角度,分析下實(shí)現(xiàn)的過(guò)程。
3、CActiveRecord
我們首先需要對(duì)3張表建立相應(yīng)的model,下面是關(guān)系表的代碼
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表(執(zhí)行查詢的那個(gè)表), 引用表為B表(外鍵所引用的表)
建議使用Gii自動(dòng)生成模型,這樣能夠節(jié)省大量時(shí)間,為了測(cè)試方便,可以對(duì)主表生成CRUD,就是增刪改查頁(yè)面,其他的引用表只用生成model就行了。
1. model函數(shù)、tablename函數(shù)用于得到這個(gè)模型和得到數(shù)據(jù)庫(kù)表基本信息。自動(dòng)生成無(wú)需修改
2.rules函數(shù),這個(gè)函數(shù)主要用于規(guī)定參數(shù)檢驗(yàn)方式,注意即使有些參數(shù)不需要校驗(yàn),也必須出現(xiàn)在rules中。不然模型將無(wú)法得到參數(shù)
3.relation函數(shù),這個(gè)函數(shù)十分關(guān)鍵,用于定義表之間的關(guān)系,下面我將詳細(xì)說(shuō)明其中含義
'relationType' => array(self::BELONGS_TO, 'SocialRelationType', 'relation_type_id')
這句代碼中結(jié)構(gòu)如下
'VarName'=>array('RelationType', 'ClassName', 'ForeignKey', ...additional options)
VarName 是關(guān)系的名字,我們以后會(huì)用這個(gè)名字訪問(wèn)外鍵引用表的字段
RelationType是關(guān)系的類型,十分重要,如果設(shè)定錯(cuò)誤會(huì)導(dǎo)致一些奇怪而且難以檢查的錯(cuò)誤,Yii一共提供了4種關(guān)系
BELONGS_TO(屬于): 如果表 A 和 B 之間的關(guān)系是一對(duì)多,則 表 B 屬于 表 A
HAS_MANY(有多個(gè)): 如果表 A 和 B 之間的關(guān)系是一對(duì)多,則 A 有多個(gè) B
HAS_ONE(有一個(gè)): 這是 HAS_MANY 的一個(gè)特例,A 最多有一個(gè) B
MANY_MANY: 這個(gè)對(duì)應(yīng)于數(shù)據(jù)庫(kù)中的 多對(duì)多關(guān)系
ClassName是引用表名,就是外鍵所引用的表的名字,也就是B表表名
ForeignKey是外鍵名,主要這里填寫(xiě)的是外鍵在主表中的名字,也就是外鍵在A表中的表名,切記不要填錯(cuò)了
如果B表中是雙主鍵可以采用下列方式實(shí)現(xiàn),從軟件工程的角度不推薦這樣的做法,每個(gè)表最好使用獨(dú)立無(wú)意義主鍵,不然容易出現(xiàn)各種問(wèn)題,而且不方便管理
'categories'=>array(self::MANY_MANY, 'Category', 'tbl_post_category(post_id, category_id)'),
additional option 附加選項(xiàng),很少用到
4 attributeLabels函數(shù),這就是表屬性的顯示名稱了,有點(diǎn)點(diǎn)像powerdesigner中code和name的關(guān)系前面一部分為數(shù)據(jù)庫(kù)字段名,后面一部分為顯示名稱
5 search函數(shù),用于生成表查詢結(jié)果的函數(shù),可以在此加一些限制條件,具體的使用方法就不在這里說(shuō)明了,可以參考API中CDbCriteria的講解。如果使用Gii生成那么不需要怎么修改。
同理我們生成,剩下的兩個(gè)引用表
關(guān)系類型表: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
三張表介紹完了后,下面就應(yīng)當(dāng)介紹Controller了,同樣的我們使用Gii生成主表(A表)的CRUD后就能得到controller,我們只需要對(duì)其進(jìn)行一些修改即可,代碼如下
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(); } } }
簡(jiǎn)單介紹下其中各個(gè)函數(shù)和變量
$layout 就是布局文件的位置了,布局文件如何使用,這里不做討論
filters 定義過(guò)濾器,這里面水很深
accessRules 訪問(wèn)方式,就是那些用戶能夠訪問(wèn)到這個(gè)模塊
array('allow', // allow all users to perform 'index' and 'view' actions 'actions'=>array('index','view'), 'users'=>array('*'), ),
allow 表示允許訪問(wèn)的規(guī)則如下,deny表示拒絕訪問(wèn)的規(guī)則如下。
action表示規(guī)定規(guī)則使用的動(dòng)作
user表示規(guī)則適用的用戶群組,*表示所有用戶,@表示登錄后的用戶,admin表示管理員用戶
actionXXX 各個(gè)action函數(shù)
這里值得注意的是 這個(gè)函數(shù)
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中設(shè)置相應(yīng)的查詢條件,注意這里設(shè)置是對(duì)于主表(A表)進(jìn)行的,用的字段名也是主表中的,因?yàn)槲覀円@示的是當(dāng)前用戶的好友,于是,這里我們使用Yii::app()->user->id取得當(dāng)前用戶的id 。
loadModel 用于裝載模型,這里我們可以看到findByPk查詢了數(shù)據(jù)庫(kù)。
performAjaxValidation 用于Ajax驗(yàn)證。
5、視圖View
index.php
<?php /* @var $this SocialRelationController */ /* @var $dataProvider CActiveDataProvider */ $this->breadcrumbs=array( 'Social Relations', ); ?> <h2>Social Relations</h2> <?php $this->widget('zii.widgets.CListView', array( 'dataProvider'=>$dataProvider, 'itemView'=>'_view', )); ?>
我們使用一個(gè) CListView控件進(jìn)行顯示,其中itemView為內(nèi)容顯示的具體表單,dataProvider這個(gè)是內(nèi)容源,我們?cè)赾ontroller中已經(jīng)設(shè)定了。
_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>
主要都是類似的,我們看其中的一條
復(fù)制代碼 代碼如下:
<b><?php echo CHtml::encode($data->getAttributeLabel('relation_type_name')); ?>:</b>
<?php echo $data->relationType->relation_type_name; ?>
第一行為顯示標(biāo)簽,在模型中我們?cè)O(shè)定的顯示名就在這里體現(xiàn)出來(lái)
第二行為內(nèi)容顯示,這里的relationType是在模型中設(shè)置的關(guān)系名字,后面的relation_type_name是引用表的字段名(B表中的名字)
6、總結(jié)
通過(guò)上面的步驟,我們就實(shí)現(xiàn)了整個(gè)聯(lián)合查詢功能,效果圖如下所示:
關(guān)于Yii中怎么實(shí)現(xiàn)多表聯(lián)合查詢操作問(wèn)題的解答就分享到這里了,希望以上內(nèi)容可以對(duì)大家有一定的幫助,如果你還有很多疑惑沒(méi)有解開(kāi),可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識(shí)。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。