How to use different connections according to query type in Yii

All my reads should go to one DB connection All my writes should go to another connection

How do I accomplish this in Yii, with minimal changing the code of the core library?

And on occasions (as stated in the comments) I will need the ability to control each Model type of connection, so read can go to the Master too.

I have written an app where the master admin panel could be used to create and administer several customer-facing "instances", so there was the need to "direct" queries running inside the master app to any one of the instance-specific databases. I 'll illustrate a trimmed-down version what I did first (which is not as demanding as your goal) and present a more powerful approach afterwards.

Using multiple databases for all queries

Directing queries to a database that has been specified from beforehand is easy: just override the CActiveRecord::getDbConnection method. What I did can be trimmed down to this:

abstract class InstanceActiveRecord extends CActiveRecord {
    public static $dbConnection = null;

    public function getDbConnection() {
        if (self::$dbConnection === null) {
            throw new CException('Database connection must be defined to work with instance records.');
        }

        return self::$dbConnection;
    }
}

So if you want to direct all operations to a specific database you simply have to derive your ActiveRecord models from InstanceActiveRecord instead of CActiveRecord, then just do InstanceActiveRecord::dbConnection = $connection and you are good to go.

Using multiple databases with auto-selection based on query type

For this you need to go deeper into CActiveRecord. It turns out that getDbConnection is mostly used by getCommandBuilder, which in turn is the method called by all the delete/update/insert families. So we need to pass some kind of context from those functions down to getDbConnection, where the choice of which connection we want to use will be made.

For this we 're going to have to override all methods in those families, so a reasonable approach might be:

Step 1. Add an optional parameter to getDbConnection and override it to return whichever connection you want it to based on the parameter value. The simplest would be something like this:

public function getDbConnection($writeContext = null) {
    if ($writeContext === null) {
        return parent::getDbConnection(); // to make sure nothing will ever break
    }

    // You need to get the values for $writeDb and $readDb in here somehow,
    // but this can be as trivially easy as you like (e.g. public static prop)
    return $writeContext ? $writeDb : $readDb;
}

Step 2. Add an optional parameter to getCommandBuilder with the same semantics and override it to forward the value:

public function getCommandBuilder($writeContext = null) {
    return $this->getDbConnection($writeContext)->getSchema()->getCommandBuilder();
}

Step 3. Find all call sites of getCommandBuilder (there will be a bunch of those) and getDbConnection (there were just 2 more than the one inside getCommandBuilder at the time I looked) and override them to specify the read/write context appropriately. Example:

public function deleteAll($condition='',$params=array()) {
    Yii::trace(get_class($this).'.deleteAll()','system.db.ar.CActiveRecord');

    // Just need to add the (true) value here to specify write context:
    $builder=$this->getCommandBuilder(true);
    $criteria=$builder->createCriteria($condition,$params);
    $command=$builder->createDeleteCommand($this->getTableSchema(),$criteria);
    return $command->execute();
}

After this you should be ready to go. There's also nothing stopping you from making a more involved context selection mechanism than the true/false option illustrated here, the concept is the same.

Practical concerns

While all of this will achieve the stated goal perfectly, there remains a question regarding the maintainability of this approach.

It's true that going this route will involve lots of copy/pasted code from CActiveRecord, which is not ideal if there's the chance of moving your app to a later version of the framework later on; to do so, you will be forced to bring your subclass up to sync with the latest version of CActiveRecord.

To migitate this and make your life easier in the future, you can consider this approach:

1. Instead of copy/pasting and overriding only part of CActiveRecord, make an exact copy (minus the properties of course) of CActiveRecord and perform the changes there. In other words, copy over even those methods that you do not intend to override.
2. Perform the changes mentioned above. Remember that this involves an override of getDbConnection and only really minor edits to a dozen or two of other places.
3. Make your models extend the resulting class.

Now when the time comes to upgrade to a later Yii version, you will need to bring your class in sync with CActiveRecord again. Fire up your favorite diff tool and compare your class with the target version of CActiveRecord. The diff tool will show you only the getDbConnection and minor edits, plus whatever changes were made to CActiveRecord in Yii's core. Copy those other changes over to your class. Problem solved in 5 minutes tops.