Yii2 framework. When I save multiple ActiveRecords in AFTER_INSERT_EVENT of another ActiveRecord, the values in the database is not updated fast enough, so old values are shown when redirect to viewing the data.
To be more specific: Standard XAMPP environment with PHP 7.2.9. I have made a trait to make it easy to have extra attributes with history in model (either existing attributes or new attributes). The trait is used on ActiveRecord.
Notice the sleep(5) in function TL_save. This handled the problem, but it is not the correct solution. How do I ensure all is updated before it is read again? I want to avoid use locks on the row as that would require alteration of a table before it can be used. Is there a way around it? Transactions - I have tried it but perhaps not correct as it had no effect. A reload of the view page also solves the problem, but again: not very classy :-)
Also: Should I share this code on GitHub? I have not done so before and are not quite sure if it would be of any value to others really.
trait TimelineTrait
{
private $timelineConfig;
public function timelineInit($config)
{
$std = [
'attributes' => [], // required
'_oldAttributes'=>[],
'datetime'=> date('Y-m-d H:i:s'),
'validationRule'=>'safe',
'table'=>$this->tableName(),
'onlyDirty'=>true, // using !=, not !==
'events'=>[
self::EVENT_AFTER_INSERT=>[$this, 'TL_EventAfterInsert'],
self::EVENT_AFTER_UPDATE=>[$this, 'TL_EventAfterUpdate'],
self::EVENT_AFTER_FIND=>[$this, 'TL_EventAfterFind'],
self::EVENT_AFTER_DELETE=>[$this, 'TL_EventAfterDelete'],
],
'TimelineClass'=>Timeline::class,
/*
Must have the following attributes
id integer primary key auto increment not null,
table varchar(64) not null,
table_id integer not null,
attribute varchar(64) not null,
datetime datetime not null
value text (can be null)
*/
];
$this->timelineConfig = array_replace_recursive($std, $config);
foreach($this->timelineConfig["events"]??[] as $trigger=>$handler)
$this->on($trigger, $handler);
}
public function __get($attr)
{
$cfg = &$this->timelineConfig;
if (in_array($attr, array_keys($cfg["attributes"])))
return $cfg["attributes"][$attr];
else
return parent::__get($attr);
}
public function __set($attr, $val)
{
$cfg = &$this->timelineConfig;
if (in_array($attr, array_keys($cfg["attributes"]))) {
$cfg["attributes"][$attr] = $val;
} else
parent::__set($attr, $val);
}
public function attributes()
{
return array_merge(parent::attributes(), $this->timelineConfig["attributes"]);
}
public function rules()
{
$temp = parent::rules();
$temp[] = [array_keys($this->timelineConfig["attributes"]), $this->timelineConfig["validationRule"]];
return $temp;
}
public function TL_EventAfterInsert($event)
{
$this->TL_save($event, true);
}
public function TL_EventAfterUpdate($event)
{
$this->TL_save($event, false);
}
private function TL_save($event, $insert)
{
$cfg = &$this->timelineConfig;
if ($cfg["onlyDirty"])
$cfg["_oldAttributes"] = $this->TL_attributesOnTime();
foreach($cfg["attributes"] as $attr=>$val) {
$a = [
'table'=>$cfg["table"],
'table_id'=>$this->id,
'attribute'=>$attr,
'datetime'=>$cfg["datetime"],
];
if ($insert)
$model=null;
else
$model = Timeline::find()->where($a)->one();
$isNew = empty($model); // this exact attribute does not exist on timeline already
if ($isNew)
$model = new $cfg["TimelineClass"]($a);
$model->value = $val;
if (!$cfg["onlyDirty"]
|| $cfg["onlyDirty"] && $model->value!=($cfg["_oldAttributes"][$attr]??\uniqid('force_true'))) {
$ok = $model->save();
if (!$ok) $this->addErrors($attr, $model->getErrorSummary());
}
}
sleep(5);
}
public function TL_EventAfterFind($event)
{
$cfg = &$this->timelineConfig;
$data = $this->TL_attributesOnTime();
foreach($data as $attr=>$val)
$cfg["attributes"][$attr] = $val;
$cfg["_oldAttributes"] = $cfg["attributes"];
}
private function TL_attributesOnTime()
{
$cfg = &$this->timelineConfig;
$timelineTable = $cfg["TimelineClass"]::tableName();
$sql = "SELECT t1.* FROM $timelineTable AS t1
LEFT JOIN (SELECT * FROM $timelineTable WHERE `table`=:table AND table_id=:table_id AND datetime<=:datetime) AS t2
ON (t1.table=t2.table and t1.table_id=t2.table_id and t1.datetime<t2.datetime AND t1.attribute=t2.attribute)
WHERE t2.id IS NULL AND t1.datetime<:datetime AND t1.table=:table AND t1.table_id=:table_id
";
$params = [
'table'=>$cfg["table"],
'table_id'=>$this->id,
':datetime'=>$cfg["datetime"],
];
$data = \Yii::$app->db->createCommand($sql,$params)->queryAll();
$data = ArrayHelper::map($data,'attribute','value');
return $data;
}
public function TL_EventAFterDelete($event)
{
$cfg = &$this->timelineConfig;
$cfg["TimelineClass"]::deleteAll([
'table'=>$cfg["table"],
'table_id'=>$event->sender->id
]);
}
}
Example of it's use:
<?php
namespace app\models;
class KeyTime extends Key
{
use \app\behaviors\TimelineTrait;
public function init()
{
parent::init();
$this->timelineInit([
'attributes'=>[
// default values for attributes
'keyid'=>'historic id', // this is existing attribute in Key model
'label'=>'mylabel', // label and color does not exist in Key model
'color'=>'red',
],
]);
}
}
The actionUpdate
public function actionUpdate($id)
{
$model = $this->findModel($id);
if ($model->load(Yii::$app->request->post()) && $model->save()) {
return $this->redirect(['view', 'id' => $model->id]);
}
return $this->render('update', [
'model' => $model,
]);
}
After many "flashes" with microtime(true) on, I found the reason it worked sometimes with sleep(1).
The answer is in TL_attributesOnTime. the last line in $sql was
WHERE t2.id IS NULL AND t1.datetime<:datetime AND t1.table=:table AND t1.table_id=:table_id
…but it should be…
WHERE t2.id IS NULL AND t1.datetime<=:datetime AND t1.table=:table AND t1.table_id=:table_id
Notice the < is changed to <= Otherwise when the record was saved in the same second as it was populated it would not be included. Hope it can help somebody else.