In Yii, can my Model read from a database view whilst creates, updates and deletes still go to the base table?
Basically, any SELECT statements I want to be directed to a specific database view (maybe with some additional aggregate columns, or to force a dirty read).
Is this possible, and how can it be done?
You may be interested in what is suggested here
Take a look also to linked posts like this one
But, in any way, I have had same problem as well, and my desired workaround was one that offer a working solution with low code cost. Of course thinking on that takes high time invest, but to have result is better than to just try things whose are not the expected.
In my case, a view gave the Read scenario some new attributes, whose had to be remove at Update or Insert ones (scenarios). Problem was that validation process, when down to core classes (CActiveRecord / CModel) requires to have Read scenario available. This means rewriting full validation methods, or dealing with two scenarios at once.
My solution is to retain Read scenario as long as possible, and then change the context when, in real, we are gonna write some data.
// At init time, it is supposed that read scenario reigns
public function init(){
$this->_tableName = 'view_name';
}
...
// Cause I found no other solution as well,
// this is my way for avoiding Yii _md private scope
protected function setMetaDataForWriting(){
$this->_tableName = 'table_name';
$this->getMetaData()->tableSchema->name = $this->_tableName;
$this->getMetaData()->tableSchema->rawName = $this->_tableName;
$columns = &$this->getMetaData()->tableSchema->columns;
$this->getMetaData()->tableSchema->columns = array_diff_key($columns,
array_flip(array('View_Added_Field1',
'View_Added_Field2',
..,
'View_Added_FieldN')));
}
// Ensure that a field for acting like primary key is present in your SQL View
public function primaryKey()
{
return 'id';
}
public function beforeSave(){
$this->setMetaDataForWriting();
return parent::beforeSave();
}
public function afterSave(){
$this->_tableName = 'view_name';
$this->refreshMetaData();
return parent::afterSave();
}
As far as I know, there is no way of doing this. You could use Query builder but you loose all model capabilites: http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder
I have asked in the forums if there is a chance to define for Yii 2 a way of setting the way all db operations are done in Model's CRUD. For example, to use stored procedures instead of standard provided select, insert update delete.
You shall be able to generate or create models of the database view(s) as you will for a table. Simply use the view name instead of table name. So when you want to view results simply call the model of database view and when you want to edit call the model of the table.
