Detailed explanation of Yii multi-table joint query operation, detailed explanation of yii joint query

This article summarizes and describes Yii multi-table joint query for your reference, the specific content is as follows

1. Implementation method of multi-table joint query

There are two ways One is to use DAO to write SQL statements to implement . This implementation is relatively easy to understand, as long as you ensure that the SQL statements are not written incorrectly. The shortcomings are also obvious. They are relatively scattered and do not conform to the recommended framework of YII. The most important shortcoming is that it is easy to make mistakes.

There is also One is to use the CActiveRecord that comes with YII to implement multi-table joint query

2. Overall framework

We need to find a user's friend relationship. The user's information is placed in the user table, the relationship between users is placed in the relationship table, and the content of the relationship is placed in the relationship type table. Obviously we only need to use the relational table as the main table to query the other two tables. I mainly analyze the implementation process from the perspective of code.

3. CActiveRecord

We first need to establish corresponding models for the three tables. The following is the code for the relational table

SocialRelation.php

<&#63;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, 
    )); 
  } 
} 

For the convenience of description, we agree that the main table is table A (the table where the query is executed), and the reference table is table B (the table referenced by the foreign key)
It is recommended to use Gii to automatically generate the model, which can save a lot of time. For the convenience of testing, CRUD can be generated for the main table, which is the add, delete, modify and query page. For other reference tables, just generate the model.
1. The model function and tablename function are used to obtain this model and basic information of the database table. Automatically generated without modification

2. rules function , this function is mainly used to specify the parameter verification method. Note that even if some parameters do not require verification, they must appear in rules. Otherwise, the model will not be able to obtain parameters

3.relation function , this function is very critical and is used to define the relationship between tables. I will explain its meaning in detail below

'relationType' => array(self::BELONGS_TO, 'SocialRelationType', 'relation_type_id')
The structure of this code is as follows
'VarName'=>array('RelationType', 'ClassName', 'ForeignKey', ...additional options)
VarName is the name of the relationship. We will use this name to access the fields of the foreign key reference table in the future

RelationType is the type of relationship, which is very important. If the setting is wrong, it will lead to some strange and difficult-to-check errors. Yii provides a total of 4 types of relationships

BELONGS_TO (belongs to): If the relationship between tables A and B is one-to-many, then table B belongs to table A
HAS_MANY (there are multiple): If the relationship between tables A and B is one-to-many, then A has multiple B
HAS_ONE (has one): This is a special case of HAS_MANY, A has at most one B
MANY_MANY: This corresponds to the many-to-many relationship in the database
ClassName is the reference table name, which is the name of the table referenced by the foreign key, which is the name of table B

ForeignKey is the name of the foreign key. The main thing you fill in here is the name of the foreign key in the main table, which is the table name of the foreign key in table A. Remember not to fill in the wrong name

If there are dual primary keys in table B, you can use the following method to implement it. This approach is not recommended from a software engineering perspective. It is best to use independent meaningless primary keys for each table, otherwise various problems will easily occur and it will be inconvenient to manage

'categories'=>array(self::MANY_MANY, 'Category', 
        'tbl_post_category(post_id, category_id)'), 

additional option additional option, rarely used
4 attributeLabels function , this is the display name of the table attribute, a bit like the relationship between code and name in powerdesigner. The first part is the database field name, and the latter part is the display name
5 search function , a function used to generate table query results. You can add some restrictions here. The specific usage method is not explained here. You can refer to the explanation of CDbCriteria in the API. . If generated using Gii, no modification is required.

In the same way, we generate the remaining two reference tables

Relationship type table: SocialRelationType.php

<&#63;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, 
    )); 
  } 
} 

User table: AccessUser.php

<&#63;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
After introducing the three tables, we should introduce the Controller. Similarly, we use Gii to generate the CRUD of the main table (Table A) to get the controller. We only need to make some modifications to it. The code is as follows

SocialRelationController.php

<&#63;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']) &#63; $_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(); 
    } 
  } 
} 

A brief introduction to each of the functions and variables
$layout is the location of the layout file. How to use the layout file will not be discussed here

filters defines filters, the water is deep here

accessRules access method means those users can access this module

array('allow', // allow all users to perform 'index' and 'view' actions 
        'actions'=>array('index','view'), 
        'users'=>array('*'), 
      ), 

allow 表示允許訪問的規(guī)則如下，deny表示拒絕訪問的規(guī)則如下。
action表示規(guī)定規(guī)則使用的動作

user表示規(guī)則適用的用戶群組，*表示所有用戶,@表示登錄后的用戶,admin表示管理員用戶

actionXXX 各個action函數(shù)

這里值得注意的是 這個函數(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中設置相應的查詢條件，注意這里設置是對于主表(A表)進行的，用的字段名也是主表中的，因為我們要顯示的是當前用戶的好友，于是，這里我們使用Yii::app()->user->id取得當前用戶的id 。

loadModel 用于裝載模型，這里我們可以看到findByPk查詢了數(shù)據(jù)庫。

performAjaxValidation 用于Ajax驗證。

5、視圖View

index.php

<&#63;php 
/* @var $this SocialRelationController */ 
/* @var $dataProvider CActiveDataProvider */ 
 
$this->breadcrumbs=array( 
  'Social Relations', 
); 
&#63;> 
 
<h1>Social Relations</h1> 
 
<&#63;php $this->widget('zii.widgets.CListView', array( 
  'dataProvider'=>$dataProvider, 
  'itemView'=>'_view', 
)); &#63;> 

我們使用一個 CListView控件進行顯示，其中itemView為內(nèi)容顯示的具體表單，dataProvider這個是內(nèi)容源，我們在controller中已經(jīng)設定了。

_view.php

<&#63;php 
/* @var $this SocialRelationController */ 
/* @var $data SocialRelation */ 
&#63;> 
 
<div class="view"> 
 
  <b><&#63;php echo CHtml::encode($data->getAttributeLabel('relation_id')); &#63;>:</b> 
  <&#63;php echo CHtml::link(CHtml::encode($data->relation_id), array('view', 'id'=>$data->relation_id)); &#63;> 
  <br /> 
 
  <b><&#63;php echo CHtml::encode($data->getAttributeLabel('relation_type_id')); &#63;>:</b> 
  <&#63;php echo CHtml::encode($data->relation_type_id); &#63;> 
  <br /> 
 
  <b><&#63;php echo CHtml::encode($data->getAttributeLabel('relation_type_name')); &#63;>:</b> 
  <&#63;php  
    echo $data->relationType->relation_type_name; 
  &#63;> 
  <br /> 
   
  <b><&#63;php echo CHtml::encode($data->getAttributeLabel('user_id')); &#63;>:</b> 
  <&#63;php echo CHtml::encode($data->user_id); &#63;> 
  <br /> 
 
  <b><&#63;php echo CHtml::encode($data->getAttributeLabel('user_name')); &#63;>:</b> 
  <&#63;php  
    echo $data->user->name; 
  &#63;> 
  <br /> 
 
  <b><&#63;php echo CHtml::encode($data->getAttributeLabel('another_user_id')); &#63;>:</b> 
  <&#63;php echo CHtml::encode($data->another_user_id); &#63;> 
  <br /> 
 
  <b><&#63;php echo CHtml::encode($data->getAttributeLabel('another_user_name')); &#63;>:</b> 
  <&#63;php 
    echo $data->anotherUser->name; 
  &#63;> 
  <br /> 
   
</div> 

主要都是類似的，我們看其中的一條
復制代碼 代碼如下:<?php echo CHtml::encode($data->getAttributeLabel('relation_type_name')); ?>:?
<?php echo $data->relationType->relation_type_name; ?>?
第一行為顯示標簽，在模型中我們設定的顯示名就在這里體現(xiàn)出來
第二行為內(nèi)容顯示，這里的relationType是在模型中設置的關系名字，后面的relation_type_name是引用表的字段名(B表中的名字)

6、總結

通過上面的步驟，我們就實現(xiàn)了整個聯(lián)合查詢功能，效果圖如下所示：

以上就是本文的全部內(nèi)容，希望對大家的學習有所幫助，也希望大家多多支持幫客之家。