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))