I have a model based on a MySQL updateable view in a multitenant SaaS application. The MySQL view is almost identical to the underlying table, the difference being it doesn’t have the MySQL username column the table has; it contains all other columns so it’s updateable and the missing column is handled by a trigger on insert and a where clause on retrieve.
The model is as follows:
class VVehicle extends CActiveRecord
{
public static function model($className=__CLASS__)
{
return parent::model($className);
}
public function tableName()
{
return 'vw_vehicle'; // a MySQL view, not a table
}
public function primaryKey()
{
return 'id'; // same as underlying table PK (int, autoincrement, etc.)
}
/**
* @return array validation rules for model attributes.
*/
...
The controller’s class is pretty standard:
class VVehicleController extends Controller
{
public $layout='//layouts/column2';
...
public function actionView($id)
{
$this->render('view',array(
'model'=>$this->loadModel($id),
));
}
public function actionCreate()
{
$model=new VVehicle;
// Uncomment the following line if AJAX validation is needed
// $this->performAjaxValidation($model);
if(isset($_POST['VVehicle']))
{
$model->attributes=$_POST['VVehicle'];
if($model->save())
$this->redirect(array('view','id'=>$model->id));
}
$this->render('create',array(
'model'=>$model,
));
}
...
public function loadModel($id)
{
$model=VVehicle::model()->findByPk($id);
if($model===null)
throw new CHttpException(404,'The requested page does not exist.');
return $model;
}
...
}
I can create a new record just fine, but when the controller redirects to view.php to display it I get an error "404 - The requested page does not exist." which I believe comes from loadModel($id).
The browser shows an incomplete URL:
http://mydomain.com/index.php?r=vVehicle/view&id=
Not having the record id causes the action to fail. I don’t understand why it isn’t being passed from the if ($model->save()) statement to the redirect right after it. As I explained, the data is saved without any issues and if I provide the id, the view.php works fine.
Thanks, but unfortunately it didn’t work. I even tried adding $model->refresh() before setting the $id. I reckon that the reason is that the model is null because that’s the condition in the loadModel($id) function. I don’t understand how it can be nulled after the save().
Start by outputting the newly assigned id after the creation request rather than redirecting. If the id attribute doesn’t have a value then it suggests something is wrong with how your database table or model is configured.
I am on to something. I found this forum post Active Record with PostgreSQL view that kind of explains what’s going on. It seems that Yii not only doesn’t recognize a primary key in a database view, but it also doesn’t recognize its sequenceName (e.g. LAST_INSERT_ID). Now I need to find how it’s called in MySQL. Any ideas are welcome…
I kept digging and it seems that I solved the problem with this addition to the model:
/**
* Overriden method with updates required after saving to table.
*/
public function afterSave()
{
if ($this->getIsNewRecord())
{
$this->id = Yii::app()->db->getLastInsertID();
}
return parent::afterSave();
}
However, I don’t know enough about Yii to tell if this is multiuser safe, as the call is to the database in general and not specific to a table. What if there are other users creating either similar or other records simultaneously? Please let me know, thanks.
