Multiple Databases And Find

Hi!

I’m using activerecord with two databases. I followed instructions given here: http://www.yiiframework.com/wiki/78/multiple-databases-and-multiple-domains/ Is it possible to execute queries using Yii’s find-methods to the both databases? And how?

Let me clarify a bit. I would like to use this:


$users = User::model()->findAll();

instead of this:


$users = Yii::app()->db2->createCommand()

    ->select('*')

    ->from('tbl_user')

    ->queryAll();

Is this possible with two databases? I would like to make queries to both of them.

Hi,

the wiki is out of date. Yii feature have envolved since Jul 30, 2010 !

Now the framework has a nice multidb support, with transparent AR calls through different dbs.

you always have to declare your dbs in config/main.php:




'components'=>array(

.........

        'db'=>array(

            'connectionString' => 'mysql:host=localhost;dbname=database1',

            'emulatePrepare' => true,

            'username' => 'root',

            'password' => 'itsasecret',

            'charset' => 'utf8',

        ),

        'db2'=>array(

            'class' => 'CDbConnection',

            'connectionString' => 'mysql:host=localhost;dbname=database2',

            'emulatePrepare' => true,

            'username' => 'root',

            'password' => 'itsasecret',

            'charset' => 'utf8',

        ),

....

),



the in you model/MyModel.php you have to declare the db connection:




/**	 

* @return CDbConnection database connection	 

*/	

public function getDbConnection()	

{		

   return Yii::app()->db; // or return Yii::app()->db2;

}



Notes:

1/ gii (giix also and maybe other generators found in extensions directory), already handle this feature: you can select the db to use for the model generation: Excellent!

2/ db system can be from differnet types (mysql, postgres and so on) and also be hosted on different servers: Brillant !

3/ all relations works transparently: Amazing !

So you can use:




$users = User::model()->with('profile')->findAll();



where User model is from db, and Profile model from db2 and User model has_one Profile declared in its relations array as ‘profile’

how simple. Thank you Yii

Thanks a lot! :)

Now save() works perfect, but couldn’t manage to get find() to work at all… Could you clarify that with(‘profile’) thing a bit? What should the ‘profile’ be inside the brackets? Can I leave it out like this:


$users = User::model()->findAll();

Hi,

as i know AR cannot handle cross database JOINs, so it’s impossible to perform eager loading of the profile with the user at the same time. It seems that


$users = User::model()->with('profile')->findAll();

is misleading in this case, and the lazy loading should be used instead.

This is what I get when I try to make a query using find:

Fatal error: Call to a member function findAll() on a non-object

Here’s the query:


$tempParts = BuildingElement::model()->findAll();

And here’s the model:




class BuildingElement extends CActiveRecord{

    

    public $user_id;

    public $name;

    public $lifetime;

    public $talo80_element_id;

    public $talo2000_element_id;

    

    /**

    * Returns the static model of the specified AR class.

    * @return User the static model class

    */

    public static function model($className = __CLASS__) {

        parent::model($className);

    }

    

    /**

     * @return string the associated database table name

     */

    public function tableName() {

        return 'structural_element';

    }

    

    /**

     * Declares the validation rules.

     * @return array validation rules for model attributes.

     */

    public function rules() {

        return array(

            array('name, lifetime', 'required'),

            array('lifetime', 'match', 'pattern' => '/^[0-9]/'),

        );

    }

    

    public function getDbConnection() {

        return Yii::app()->db2;

    }

}



@MadAnd: I do not have my dev computer here (I am on hollidays) but, it seems to me that I have done something like eager loading …

@RuliTheHat: strange.

If $user_id;$name;$lifetime … are in your ‘structural_element’ table, you do not have to declare them in the Model definition

I’m using PHP version 5.3.16, could that be the reason?

Also, if I create an object from the BuildingElement class, and call the find-method through it, everything works just fine…

Like this:




$element_model = new BuildingElement;

$element_data = $element_model->findAll();



I have a strange scenario.

Here the db was changed, but I still use the old db as db2 to check on old records. But I need to use the find function.




Inscricao::model()->findByAttributes(array('cod_inscricao' => $cod_inscricao, 'cod_evento' => $cod_evento))



is it possible to do something like




Inscricao::model()->db2->findByAttributes(array('cod_inscricao' => $cod_inscricao, 'cod_evento' => $cod_evento))