Yii 1.1: How to log changes of ActiveRecords?

48 followers

A simple and effective way to keep track what your users are doing within your application is to log their activities related to database modifications. You can log whenever a record was inserted, changed or deleted, and also when and by which user this was done. For a CActiveRecord Model you could use a behavior for this purpose. This way you will be able to add log functionality to ActiveRecords very easily.

First of all you have to create a table for the log-lines in the database. Here is an example (MySQL):

CREATE TABLE ActiveRecordLog (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  description VARCHAR(255) NULL,
  action VARCHAR(20) NULL,
  model VARCHAR(45) NULL,
  idModel INTEGER UNSIGNED NULL,
  field VARCHAR(45) NULL,
  creationdate TIMESTAMP NOT NULL,
  userid VARCHAR(45) NULL,
  PRIMARY KEY(id)
)
TYPE=InnoDB;

The next step would be to create the corresponding Model using the YII Shell Tool:

model ActiveRecordLog

If you would like to have basic crud functionality created, you should also type:

crud ActiveRecordLog

To be able to log the changes we will use a behavior class. So you have to create one (i.e. ActiveRecordLogableBehavior) and store it somewhere in your application directory (i.e. \protected\behaviors). The behavior class should extend CActiveRecordBehavior as we want to work with ActiveRecords here.

class ActiveRecordLogableBehavior extends CActiveRecordBehavior
{
    private $_oldattributes = array();
 
    public function afterSave($event)
    {
        if (!$this->Owner->isNewRecord) {
 
            // new attributes
            $newattributes = $this->Owner->getAttributes();
            $oldattributes = $this->getOldAttributes();
 
            // compare old and new
            foreach ($newattributes as $name => $value) {
                if (!empty($oldattributes)) {
                    $old = $oldattributes[$name];
                } else {
                    $old = '';
                }
 
                if ($value != $old) {
                    //$changes = $name . ' ('.$old.') => ('.$value.'), ';
 
                    $log=new ActiveRecordLog;
                    $log->description=  'User ' . Yii::app()->user->Name 
                                            . ' changed ' . $name . ' for ' 
                                            . get_class($this->Owner) 
                                            . '[' . $this->Owner->getPrimaryKey() .'].';
                    $log->action=       'CHANGE';
                    $log->model=        get_class($this->Owner);
                    $log->idModel=      $this->Owner->getPrimaryKey();
                    $log->field=        $name;
                    $log->creationdate= new CDbExpression('NOW()');
                    $log->userid=       Yii::app()->user->id;
                    $log->save();
                }
            }
        } else {
            $log=new ActiveRecordLog;
            $log->description=  'User ' . Yii::app()->user->Name 
                                    . ' created ' . get_class($this->Owner) 
                                    . '[' . $this->Owner->getPrimaryKey() .'].';
            $log->action=       'CREATE';
            $log->model=        get_class($this->Owner);
            $log->idModel=      $this->Owner->getPrimaryKey();
            $log->field=        '';
            $log->creationdate= new CDbExpression('NOW()');
            $log->userid=       Yii::app()->user->id;
            $log->save();
        }
    }
 
    public function afterDelete($event)
    {
        $log=new ActiveRecordLog;
        $log->description=  'User ' . Yii::app()->user->Name . ' deleted ' 
                                . get_class($this->Owner) 
                                . '[' . $this->Owner->getPrimaryKey() .'].';
        $log->action=       'DELETE';
        $log->model=        get_class($this->Owner);
        $log->idModel=      $this->Owner->getPrimaryKey();
        $log->field=        '';
        $log->creationdate= new CDbExpression('NOW()');
        $log->userid=       Yii::app()->user->id;
        $log->save();
    }
 
    public function afterFind($event)
    {
        // Save old values
        $this->setOldAttributes($this->Owner->getAttributes());
    }
 
    public function getOldAttributes()
    {
        return $this->_oldattributes;
    }
 
    public function setOldAttributes($value)
    {
        $this->_oldattributes=$value;
    }
}

The behavior class uses the ActiveRecordLog Model to store the log lines into the database. It will log a line each time a record is inserted or deleted. It will also log a line for each field which is changed.

In order to make an ActiveRecord Model use this behavior, you have to add the following code to the Model class:

public function behaviors()
{
    return array(
        // Classname => path to Class
        'ActiveRecordLogableBehavior'=>
            'application.behaviors.ActiveRecordLogableBehavior',
    );
}

Of course this simple example could be enhanced:

  • support for mult-column primary keys
  • savethe attributeLabels instead of the field names
  • make description customizable
  • and so on...

Total 7 comments

#17109 report it
jerome nicholas at 2014/05/05 12:35am
config/main import

needs

import => array( .....
'application.behaviors.ActiveRecordLogableBehavior', )

#16365 report it
Alex D. at 2014/02/14 06:15pm
Thanx!

Great article! Just what I've needed!

#13090 report it
Alessandro Correia at 2013/05/03 12:35pm
Great

I will try. Thank U so much!

#7621 report it
acorncom at 2012/04/03 12:21pm
Note re: problems with PHP 5.2 and the beforeSave function suggested by le_top

You'll run into glitches with how $class::model() works in PHP 5.2, as that usage isn't supported in PHP 5.2 This code will work instead:

public function beforeSave($event) {
    $attr = $this->getOldAttributes();
    if(!$this->Owner->isNewRecord && empty($attr)) {
        $thisModel = call_user_func(array(get_class($this->Owner), 'model'));
        $this->_oldattributes = $thisModel->findByPk($this->owner->getPrimaryKey())->attributes;
    }
 
    return parent::beforeSave($event);
}
#7127 report it
le_top at 2012/02/25 09:42am
Changes to the module to make it work.

It turned out that I had to make several changes to make this work. Basically the install is a four-step process:

  1. Add the extension/module to the configuration [explained above]

  2. Create the database. -> some changes.

  3. Add the behavior to the desired classes. [explained above]

  4. Make some "corrections" to the code -> some changes.

So the changes:

2) a) The creation of the data base is proposed above as a manual operation, but also as a migrate operation. I wanted to try/use that migrate functionality and I had some trouble on the mySQL database. First, I had to copy the migrations file to the protected/migrations directory. I'ld expect that this would be found automatically in the extension, but that was not the case. A look at the core code did not seem to indicate another way to do that. I aldo had to modify the migrations file because 'string' was not accepted and some indexes were not allowed. I made changes like this:

$this->createTable( 'tbl_audit_trail',
            array(
                'id' => 'pk',
                'old_value' => 'text',
                'new_value' => 'text',
                'action' => 'VARCHAR(20) NOT NULL',
                'model' => 'VARCHAR(45) NOT NULL',
                'field' => 'VARCHAR(45) NOT NULL',
                'stamp' => 'datetime NOT NULL',
                'user_id' => 'VARCHAR(45)',
                'model_id' => 'VARCHAR(45) NOT NULL',
            )
        );
 
        //Index these bad boys for speedy lookups
        $this->createIndex( 'idx_audit_trail_user_id', 'tbl_audit_trail', 'user_id');
        $this->createIndex( 'idx_audit_trail_model_id', 'tbl_audit_trail', 'model_id');
        $this->createIndex( 'idx_audit_trail_model', 'tbl_audit_trail', 'model');
        $this->createIndex( 'idx_audit_trail_field', 'tbl_audit_trail', 'field');
        //$this->createIndex( 'idx_audit_trail_old_value', 'tbl_audit_trail', 'old_value');
        //$this->createIndex( 'idx_audit_trail_new_value', 'tbl_audit_trail', 'new_value');
        $this->createIndex( 'idx_audit_trail_action', 'tbl_audit_trail', 'action');

I basically change string to varchar and removed index creation on the old and new values.

2) b) Perform the migration using the command line tool. Basically 'yiic migrate' or '<path/to/php-executeable> yiic.php migrate'. And then accept the migration.

4) Finally I also had to make some changes to the auditTrail code. Not all changes were recorded as expected and the 'oldvalues' were absent in most of my cases (resulting in more audit entries than needed too).

Therefore, in LoggableBehavior, I extend the'beforeSave' method. My issue '$this->_oldattributes' was empty. 'afterFind' did not seem to be executed in all relevant cases - I did not identify why (I looked, but the search for it was longer than expected). Basically, if the oldattributes are empty, this looks up the original record in the database to get the old attributes.

public function beforeSave($event) {
        $attr=$this->getOldAttributes();
        if(!$this->Owner->isNewRecord&&empty($attr)) {
            $class=get_class($this->Owner);
            $this->_oldattributes=$class::model()->findByPk($this->owner->getPrimaryKey())->attributes;
        }
 
        return parent::beforeSave($event);
    }

That's it.

#4653 report it
MadSkillsTisdale at 2011/07/31 09:58pm
I made this into an extension so it is accessible to everyone

I turned this tutorial into an extension. I included migrations for table creation, and I have modified it to include an admin widget and made it a little more extensible. If anyone is interested, please check it out here:

http://www.yiiframework.com/extension/audittrail

Also, thank you so much to pfth for writing this wiki in the first place.

#3830 report it
roy sarango at 2011/05/12 01:41pm
Awesome!

Thanks for this great extension and for the detailed explanations also :) I just modified to accomplish a client's requirement. I used it for saving changes on one specific model (Historical Info). Fields in main model are identical to the historical model except by id and date, those 2 were added in the latter (not present in the former) for obvious reasons. Here's my code... I hope it's useful for someone.

<?php
class HistorialPaciente extends CActiveRecordBehavior
{
    private $_oldattributes = array();
    public function afterSave($event)
    {
        if (!$this->Owner->isNewRecord) {
 
            // new attributes
            $newattributes = $this->Owner->getAttributes();
            $oldattributes = $this->getOldAttributes();
 
            $log=new CambioPaciente;
            $cont = 0;
            // compare old and new
            foreach ($newattributes as $name => $value) {
                if (!empty($oldattributes)) {
                    $old = $oldattributes[$name];
                } else {
                    $old = '';
                }
 
                if ($value != $old) {
                    $cont = $cont + 1;
                    $log->$name = $old;
                }
            }
            if ($cont <> 0)
                $log->fecha = time();
                $log->save();
        } 
    }
 
 
    public function afterFind($event)
    {
        $this->setOldAttributes($this->Owner->getAttributes());
    }
 
    public function getOldAttributes()
    {
        return $this->_oldattributes;
    }
 
    public function setOldAttributes($value)
    {
        $this->_oldattributes=$value;
    }
}
?>

Leave a comment

Please to leave your comment.

Write new article
  • Written by: pfth
  • Updated by: Yang He
  • Category: Tutorials
  • Yii Version: 1.1
  • Votes: +37 / -2
  • Viewed: 34,185 times
  • Created on: Feb 13, 2009
  • Last updated: Jun 29, 2012
  • Tags: Logging