Yii多表联合查询操作详解

本文针对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,     ));   } }