[Yii2笔记]016 AR跨数据库的关联(Cross-Database Relations) [ 技术分享 ]

说明

学习Yii Framework 2（易2框架）的过程是漫长的，也是充满乐趣的，以下是我学习Yii2框架时对官网英文资料(请参见原文网址)的翻译和代码实现，提供了较完整的代码，供你参考。不妥之处，请多多指正！

原文网址：

http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#cross-database-relations
12、Cross-Database Relations(跨数据库的关联)

本文主题：AR跨数据库的关联(Cross-Database Relations)

12、跨数据库的关联(Cross-Database Relations) Active Record可以定义使用不同数据库的AR类的关联，数据库可以是不同类型的（如MySQL和PostgreSQL，或MS SQL和MongoDB），并且它们可以运行在不同的服务器上，你可以使用相同的格式执行关联查询，例如：

// Customer is associated with the "customer" table in a relational database (e.g. MySQL)
class Customer extends \yii\db\ActiveRecord
{
    public static function tableName()
    {
        return 'customer';
    }

    public function getComments()
    {
        // a customer has many comments
        return $this->hasMany(Comment::className(), ['customer_id' => 'id']);
    }
}

// Comment is associated with the "comment" collection in a MongoDB database
class Comment extends \yii\mongodb\ActiveRecord
{
    public static function collectionName()
    {
        return 'comment';
    }

    public function getCustomer()
    {
        // a comment has one customer
        return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }
}

$customers = Customer::find()->with('comments')->all();

你可以使用本章所描述的大多数关联查询特性。

注意：joinWith（）的用法仅限于允许跨数据库JOIN查询的数据库。正因如此，你不能在上例中使用此方法，因为MongoDB不支持JOIN。

跨数据库关联属性实例：

文件位置：

D:\phpwork\basic\models\Reader.php

源代码：

namespace app\models;
class Reader extends \yii\db\ActiveRecord{
    public static function getDb(){
        return \Yii::$app->db;
    }
    public function rules()
    {
        return [
            [['name','level'],'required'],
            [$this->attributes(),'safe'],
        ];
    }
    public static function readerAll()
    {
        $query = new \yii\db\Query;
        $result = $query
            ->from(static::tableName())
            ->all();
        return $result;
    }
    public function getArticles()
    {
        return $this->hasMany(ArticleInfo::className(),['reader' => 'id']);
    }
}

文件位置：

D:\phpwork\basic\controllers\ReaderController.php

源代码：

	public function actionArticles($id) {
        if($id){
              $model=Reader::findOne(['id'=>$id]);
             if($model){
                $articles=$model->articles;
            }else{
                $this->error('No such tag');
            }
           return $this->render("articles",[
                'articles'=>$articles,
                'model'=>$model,
            ]);

        }
    }

文件位置：

D:\phpwork\basic\config\web.php

源代码：

$config = [
    'id' => 'basic',
    'basePath' => dirname(__DIR__),
    'bootstrap' => ['log'],
    'components' => [
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=yiibasic',
            'username' => 'yiiuser',
            'password' => 'jkljl23asdf23oickxvzklqwer',
            'charset' => 'utf8',
        ],
        'mongodb' => [
            'class' => 'yii\mongodb\Connection',
            'dsn' => 'mongodb://yiibaseuser:ycj123456@127.0.0.1:27017/yiibase',
        ],
		......
	],
];

文件位置：

D:\phpwork\basic\models\ArticleInfo.php

源代码：

namespace app\models;
class ArticleInfo extends \yii\mongodb\ActiveRecord{
    public function attributes()
    {
        return [
            '_id',
            'title',
            'content',
            'authInfo',
            'authInfo.id',
            'authId',
            'createInfo',
            'updateInfo',
            'isDeleted',
            'level',
            'click',
            'reader',
        ];
    }
    public function rules(){
        return [
            [['title','content','authInfo','createInfo','updateInfo','isDeleted','level'],'required'],
            ['level','levelCheck'],
            ['click','default','value'=>1],
            [$this->attributes(),'safe'],
        ];
    }
    public function levelCheck($attribute)
    {
        if(!is_numeric($this->$attribute)){
            $this->addError($attribute, 'Article level must be a integer!');
        }else{
            $this->$attribute=(int)$this->$attribute;
        }
    }
    public static function articleAll()
    {
        $query = new \yii\mongodb\Query;
        $result = $query
            ->where(['isDeleted'=>false])
            ->from(static::collectionName())
            ->all();
        foreach ($result as $k=>$item) {
            $result[$k]['id']=(string)$item['_id'];
        }
        return $result;
    }

    public static function findOne($con)
    {
        if(is_array($con)){
            $con=array_merge($con,['isDeleted'=>false]);
        }else{
            $con=['_id'=>$con,'isDeleted'=>false];
        }
        return parent::findOne($con);
    }
    public function getAuthor()
    {
        return $this->hasOne(AuthorInfo::className(), ['_id' => 'authId']);
    }

    public function getArticleTags()
    {
        return $this->hasMany(ArticleTag::className(), ['articleId' => '_id']);
    }
    public function getAllTags()
    {
        return $this->hasMany(Tags::className(), ['_id' => 'tagId'])
            ->select(['tag'])
            ->asArray()
            ->via('articleTags');
    }
    public function getReaderdata()
    {
        return $this->hasOne(Reader::className(), ['id' => 'reader'])->select(['name'])->asArray();
    }
}

文件位置：

D:\phpwork\basic\controllers\ArticleController.php

源代码：

   public function actionEditArticle($id='')
    {
        $model=new Article(['id'=>$id]);
        if ($model->load(Yii::$app->request->post())) {
            if (Yii::$app->request->isAjax) {
                Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;
                return \yii\bootstrap\ActiveForm::validate($model);
            }
            if($model->validate()){
                $errorArr=$model->save();
                if($errorArr){
                    $this->errorDisplay($errorArr);
                }else{
                    return $this->redirect(['index']);
                }
            }else{
                $this->errorDisplay($model->getErrors());
            }
        }else{
            $author=AuthorInfo::authorAll();
            $reader=Reader::readerAll();
            if(!$model->initInfo()){
                return $this->redirect(['edit-article']);
            }
            return $this->render('editArticle',['model'=>$model,'author'=>$author,'reader'=>$reader]);
        }
    }

文件位置：

D:\phpwork\basic\models\Article.php

源代码：

namespace app\models;
use yii\base\Model;
use yii\helpers\ArrayHelper;

class Article extends Model{
    public $title,$content,$authId,$isNewRecord,$id,$level,$tags,$reader;

    public function rules()
    {
        return [
            ['title','trim'],
            ['tags','trim'],
            ['tags','filter','filter'=>function($value){
                return \str_replace("，",",",$value);
            }],
            ['title', 'validateTitle'],
            [$this->attributes(),'safe'],
        ];
    }

    public function validateTitle($attribute){
        $where=['and',['isDeleted'=>false],['title'=>$this->$attribute]];
        if(!$this->isNewRecord){
            //_id查询时只能使用'not in'，不能使用'!='或'<>'
            $where[]=['not in','_id',$this->id];
        }
        $query = new \yii\mongodb\Query;
        $result = $query->select(['_id'])
            ->where($where)
            ->from(ArticleInfo::collectionName())
            ->limit(1)
            ->one();
        if($result){
            $this->addError($attribute, 'The title "'.$this->$attribute.'" have exists!');
        }
    }
    public function init()
    {
        parent::init();
        $this->isNewRecord=empty($this->id)?true:false;
    }

    public function attributeLabels() {
        return [
            'title'=>'标题',
            'content'=>'内容',
            'level'=>'等级',
            'authId'=>'作者',
            'tags'=>'标签',
            'reader'=>'读者',
        ];
    }
    public function save()
    {
        $errorArr=[];
        if($this->isNewRecord){
            //创建新记录
            $info=new ArticleInfo();
            $info->createInfo=self::getCreateInfo();
            $info->isDeleted=false;
            $tagsOld='';
        }else{
            //保存原有记录的修改信息
            $info=ArticleInfo::findOne(['_id'=>$this->id]);
            if(!$info){
                $errorArr=[['Article is empty!']];
            }else{
                $tagsOld=$info->allTags;
            }
        }
        if(!$errorArr){
			//使用MongoDB进行数据读取
            $author=AuthorInfo::findOne(['_id'=>$this->authId]);
            if(!$author){
                $errorArr=[['Author is empty!']];
            }
        }
        if(!$errorArr){
			//使用MariaDB进行数据读取
            $reader=Reader::findOne(['id'=>$this->reader]);
            if(!$author){
                $errorArr=[['Reader is empty!']];
            }else{
                $reader=$reader['id'];
            }
        }
        if(!$errorArr){
            $authorInfo=[
                'id'=>$author['_id'],
                'name'=>$author['name'],
                'level'=>$author['level'],
            ];
            $info->authInfo=$authorInfo;
            $info->authId=$author['_id'];//必须存成对象类型："authId" : ObjectId("58a14587e4017a1c1600002a")
            //$info->authId=(string)$author['_id'];//存成字符串，则AuthorInfo的关联属性getArticles()将失效！
            $info->reader=$this->reader;
            $info->title=$this->title;
            $info->content=$this->content;
            $info->updateInfo=self::getUpdateInfo();
            $info->level=$this->level;
            if(!$info->save()){
                $errorArr=$info->getErrors();
            }else{
                //处理标签
               \app\models\ArticleTag::saveData($info->_id,$this->tags,$tagsOld);
            }
        }
        return $errorArr;
    }

    public static function getCreateInfo()
    {
        return [
            'time'=>time(),
            'ip'=>\Yii::$app->request->userIP,
        ];
    }
    public static function getUpdateInfo()
    {
        return [
            'time'=>time(),
            'ip'=>\Yii::$app->request->userIP,
        ];
    }

    public function initInfo()
    {
        if(!$this->isNewRecord){
            $article=ArticleInfo::findOne(['_id'=>$this->id]);
            if($article){
                $this->title=$article->title;
                $this->content=$article->content;
                $this->level=$article->level;
                $this->reader=$article->reader;
                $this->authId=ArrayHelper::getValue($article,'authInfo.id','');
                $this->tags=implode(',',ArrayHelper::getColumn($article->allTags,'tag'));
            }else{
                //如果不存在则自动转成新建
                return false;
            }
        }
        if($this->isNewRecord){
            //新建记录初始化
        }
        return true;
    }
}

文件位置：

D:\phpwork\basic\views\news\editArticle.php

源代码：

<?php
use yii\helpers\Html;
use yii\bootstrap\ActiveForm;
use yii\helpers\ArrayHelper;
?>
<div class="country-form">
    <?php
     echo "编辑文章";
    echo "&nbsp;&nbsp;&nbsp;&nbsp;<a href='/news'>返回</a><br>";
    ?>
    <?php
    $form = ActiveForm::begin();
    ?>
    <?= $form->field($model, 'title',['enableAjaxValidation' => true])->textInput() ?>
    <?= $form->field($model, 'content')->textInput() ?>
    <?= $form->field($model, 'level')->textInput() ?>
    <?= $form->field($model, 'authId')->dropDownList(ArrayHelper::map($author,'id', 'name')) ?>
    <div class="form-group">
        <?= Html::submitButton($model->isNewRecord ? Yii::t('app', 'Create') : Yii::t('app', 'Update'), ['class' => $model->isNewRecord ? 'btn btn-success' : 'btn btn-primary']) ?>
    </div>
    <?php ActiveForm::end(); ?>
</div>

（全文完）