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

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

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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(); 
    } 
  } 
}

Copy after login

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('*'), 
      ),

Copy after login

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

Copy after login

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

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

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;>

Copy after login

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

_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>

Copy after login

主要都是类似的，我们看其中的一条
复制代码代码如下:<?php echo CHtml::encode($data->getAttributeLabel('relation_type_name')); ?>:
<?php echo $data->relationType->relation_type_name; ?>
第一行为显示标签，在模型中我们设定的显示名就在这里体现出来
第二行为内容显示，这里的relationType是在模型中设置的关系名字，后面的relation_type_name是引用表的字段名(B表中的名字)

6、总结

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