Will this work? Changing model DB on the fly?

Yii 2.0 General Discussions
1

I have several sites using the same set of scripts.

Each sites has dozens of models (all the same).

The only thing that needs to change is the db connection.

I need to access the same model using two different databases in one controller action.

I define the connections in the app config: db,dbBETA,dbPPV,dbVIDEOS,…

I created a base model to override getDb()…


class AbstractKvs extends \yii\db\ActiveRecord

{

    public static $dbid;


// FAILS AT /yiisoft/yii2/db/BaseActiveRecord.php on line 1114  (no config given for "new static".

//    public function __construct(array $config)

//    {

//        parent::__construct($config);

//

//        if(isset($config['dbid'])){

//            self::setDbid($config['dbid']);

//        }

//    }




    public static function getDb()

    {

        switch(self::$dbid)

        {

            default:

            case 'PPV':

                return \Yii::$app->dbPPV;


            case 'BETA':

                return \Yii::$app->dbBETA;


            case 'VIDEOS':

                return \Yii::$app->dbVIDEOS;


        }

    }


    // dbid is a string

    public  function setDbId($dbid)

    {

        self::$dbid = $dbid;

    }

So I can tell the ActiveRecord which connection to use at any time.

Now this appears to be working, but I am terrified it will break at some deeper level.




    public function actionTest()

    {

        $Users = new KVSUsers();

        $Users->setDbId('VIDEOS');

        $U = $Users->findOne(60795);

        echo '<br> VIDEOS USER 60795=' . $U->username;


        $Users->setDbId('PPV');

        $U = $Users->findOne(33);

        echo '<br> PPV USER 33=' . $U->username;


        $Users->setDbId('BETA');

        $U = $Users->findOne(3);

        echo '<br> BETA USER 3=' . $U->username;

My code works, it displays the expected user names from 3 different site/databases using the same model.

But have I signed up for an ugly surprise down the road?

Thanks for an advice.

2

You could do it like this:




$U = $Users->find()->where(['id' => 60795])->one($yourDbConnection);

http://www.yiiframework.com/doc-2.0/yii-db-activequeryinterface.html#one()-detail

3

Thank you Patrick. I see the docs say

"The DB connection used to create the DB command. If null, the DB connection returned by \yii\db\modelClass will be used."

So I think both methods produce the same result.

You have calmed my fear of breaking something internal, thank you.

Still, it seems a little unnerving. Why not just a setDb() on the model ?

Somebody has thought further ahead than me.

Or just felt it was too easy and obvious to mention to add it yourself…


class AbstractKvs extends \yii\db\ActiveRecord

{

    public static $connection; // yii\db\Connection


    public static function getDb()

    {

        return self::$connection;

    }

    public  function setDb($connection)

    {

        self::$connection = $connection;

        return $this;

    }

}
4

If you use one database most of them time and the other ones only occasionally, I’d prefer to pass the db as a parameter each time.

That way it is more explicit which connection is being used for the query. Setting the connection earlier might give you a bit of headache if you "forget" which connection you pointed the model to.

5

Understood, thank you.

There will be places and cases for both methods.

(So I will have twice as much to forget…)

6

You could also change the db connection before yii builds the application in the index.php file and leave all of your code alone. You can also inject other configs if need be.





$application = new yii\web\Application(

        yii\helpers\ArrayHelper::merge(

                [

            'components' => [

                'db' => [

                    'class' => 'yii\db\Connection',

                    'dsn' => 'mysql:host=localhost;dbname=db2',

                    'username' => 'demo',

                    'password' => 'demo',

                ]

            ]

                ], require(__DIR__ . '/../config/main.php')

        )

);

$application->run();

you could also put the array into a file. You would need to remove the db from the other config file so it doesn’t conflict. Just another way of doing things. I think this way is much simpler. You can also dynamically include modules this way.

7

Maybe you missed this?

I need to access the same model using two different databases in one controller action.

If I understand, your solution permits one db per entry script. That would make the db static for the run. I don’t see “dynamic” ie “while app is running”.

The solutions given allow for the same controller/action+model to use a different db either per query/statement or for a block of code bracketed by setDb().

Good to cover all the options, thank you.

8

i thought you had the same yii2 app on multiple domains.

www.site1.com

or

sub1.site.com

sub2.site.com

you could also set both dbs in the config i.e.

the main one is db and the alternative one is db2 (or whatever you want) and only set to db2 when needed and if it’s not set is will use db by default.

9

For the record, the solution was duh simple. I just had a mental block.

There is no need to have getDb() as part of the base class. Move it to the "last" final instantiated class.




class VoodooBase

{

    // field definitions (table columns)

}

class SiteA extends VodooBase

{

    public static getDb() {...}

}

class SiteB extends VodooBase

{

    public static getDb() {...}

}