程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> 網頁編程 >> PHP編程 >> PHP綜合 >> Yii多表聯合查詢操作詳解

Yii多表聯合查詢操作詳解

編輯:PHP綜合

本文針對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、總結

通過上面的步驟,我們就實現了整個聯合查詢功能,效果圖如下所示:

以上就是本文的全部內容,希望對大家的學習有所幫助,也希望大家多多支持。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved