Advanced Topics

Agile Data allow you to implement various tricks.

Audit Fields

If you wish to have a certain field inside your models that will be automatically changed when the record is being updated, this can be easily implemented in Agile Data.

I will be looking to create the following fields:

  • created_dts
  • updated_dts
  • created_by_user_id
  • updated_by_user_id

To implement the above, I’ll create a new class:

class Controller_Audit {

    use \atk4\core\InitializerTrait {
        init as _init;
    }
    use \atk4\core\TrackableTrait;
    use \atk4\core\AppScopeTrait;

}

TrackableTrait means that I’ll be able to add this object inside model with $model->add(new Controller_Audit()) and that will automatically populate $owner, and $app values (due to AppScopeTrait) as well as execute init() method, which I want to define like this:

public function init() {
    $this->_init();

    if(isset($this->owner->no_audit)){
        return;
    }

    $this->owner->addField('created_dts', ['type'=>'datetime', 'default'=>date('Y-m-d H:i:s')]);

    $this->owner->hasOne('created_by_user_id', 'User');
    if(isset($this->app->user) and $this->app->user->loaded()) {
        $this->owner->getElement('created_by_user_id')->default = $this->app->user->id;
    }

    $this->owner->hasOne('updated_by_user_id', 'User');

    $this->owner->addField('updated_dts', ['type'=>'datetime']);

    $this->owner->addHook('beforeUpdate', function($m, $data) {
        if(isset($this->app->user) and $this->app->user->loaded()) {
            $data['updated_by'] = $this->app->user->id;
        }
        $data['updated_dts'] = date('Y-m-d H:i:s');
    });
}

In order to add your defined behavior to the model. The first check actually allows you to define models that will bypass audit altogether:

$u1 = new Model_User($db);   // Model_User::init() includes audit

$u2 = new Model_User($db, ['no_audit' => true]);  // will exclude audit features

Next we are going to define ‘created_dts’ field which will default to the current date and time.

The default value for our ‘created_by_user_id’ field would depend on a currently-logged in user, which would typically be accessible through your application. AppScope allows you to pass $app around through all the objects, which means that your Audit Controller will be able to get the current user.

Of course if the application is not defined, no default is set. This would be handy for unit tests where you could manually specify the value for this field.

The last 2 fields (update_*) will be updated through a hook - beforeUpdate() and will provide the values to be saved during save(). beforeUpdate() will not be called when new record is inserted, so those fields will be left as “null” after initial insert.

If you wish, you can modify the code and insert historical records into other table.

Soft Delete

Most of the data frameworks provide some way to enable ‘soft-delete’ for tables as a core feature. Design of Agile Data makes it possible to implement soft-delete through external controller. There may be a 3rd party controller for comprehensive soft-delete, but in this section I’ll explain how you can easily build your own soft-delete controller for Agile Data (for educational purposes).

Start by creating a class:

class Controller_SoftDelete {

    use \atk4\core\InitializerTrait {
        init as _init;
    }
    use \atk4\core\TrackableTrait;

    function init() {
        $this->_init();

        if(isset($this->owner->no_soft_delete)){
            return;
        }

        $this->owner->addField('is_deleted', ['type'=>'boolean']);

        if (isset($this->owner->deleted_only)) {
            $this->owner->addCondition('is_deleted', true);
            $this->owner->addMethod('restore', $this);
        }else{
            $this->owner->addCondition('is_deleted', false);
            $this->owner->addMethod('softDelete', $this);
        }
    }

    function softDelete($m) {
        if (!$m->loaded()) {
            throw new \atk4\core\Exception(['Model must be loaded before soft-deleting', 'model'=>$m]);
        }

        $id = $m->id;
        if ($m->hook('beforeSoftDelete') === false) {
            return $m;
        }

        $rs = $m->reload_after_save;
        $m->reload_after_save = false;
        $m->save(['is_deleted'=>true])->unload();
        $m->reload_after_save = $rs;

        $m->hook('afterSoftDelete', [$id]);
        return $m;
    }

    function restore($m) {
        if (!$m->loaded()) {
            throw new \atk4\core\Exception(['Model must be loaded before restoring', 'model'=>$m]);
        }

        $id = $m->id;
        if ($m->hook('beforeRestore') === false) {
            return $m;
        }

        $rs = $m->reload_after_save;
        $m->reload_after_save = false;
        $m->save(['is_deleted'=>false])->unload();
        $m->reload_after_save = $rs;

        $m->hook('afterRestore', [$id]);
        return $m;
    }
}

This implementation of soft-delete can be turned off by setting model’s property ‘deleted_only’ to true (if you want to recover a record).

When active, a new field will be defined ‘is_deleted’ and a new dynamic method will be added into a model, allowing you to do this:

$m = new Model_Invoice($db);
$m->load(10);
$m->softDelete();

The method body is actually defined in our controller. Notice that we have defined 2 hooks - beforeSoftDelete and afterSoftDelete that work similarly to beforeDelete and afterDelete.

beforeSoftDelete will allow you to “break” it in certain cases to bypass the rest of method, again, this is to maintain consistency with the rest of before* hooks in Agile Data.

Hooks are called through the model, so your call-back will automatically receive first argument $m, and afterSoftDelete will pass second argument - $id of deleted record.

I am then setting reload_after_save value to false, because after I set ‘is_deleted’ to false, $m will no longer be able to load the record - it will fall outside of the DataSet. (We might implement a better method for saving records outside of DataSet in the future).

After softDelete active record is unloaded, mimicking behavior of delete().

It’s also possible for you to easily look at deleted records and even restore them:

$m = new Model_Invoice($db, ['deleted_only'=>true]);
$m->load(10);
$m->restore();

Note that you can call $m->delete() still on any record to permanently delete it.

Soft Delete that overrides default delete()

In case you want $m->delete() to perform soft-delete for you - this can also be achieved through a pretty simple controller. In fact I’m reusing the one from before and just slightly modifying it:

class Controller_SoftDelete {

    use \atk4\core\InitializerTrait {
        init as _init;
    }
    use \atk4\core\TrackableTrait;

    function init() {
        $this->_init();

        if(isset($this->owner->no_soft_delete)){
            return;
        }

        $this->owner->addField('is_deleted', ['type'=>'boolean']);

        if (isset($this->owner->deleted_only)) {
            $this->owner->addCondition('is_deleted', true);
            $this->owner->addMethod('restore', $this);
        } else {
            $this->owner->addCondition('is_deleted', false);
            $this->owner->addHook('beforeDelete', [$this, 'softDelete'], null, 100);
        }
    }

    function softDelete($m) {
        if (!$m->loaded()) {
            throw new \atk4\core\Exception(['Model must be loaded before soft-deleting', 'model'=>$m]);
        }

        $id = $m->id;

        $rs = $m->reload_after_save;
        $m->reload_after_save = false;
        $m->save(['is_deleted'=>true])->unload();
        $m->reload_after_save = $rs;

        $m->hook('afterDelete', [$id]);

        $m->breakHook(false); // this will cancel original delete()
    }

    function restore($m) {
        if (!$m->loaded()) {
            throw new \atk4\core\Exception(['Model must be loaded before restoring', 'model'=>$m]);
        }

        $id = $m->id;
        if ($m->hook('beforeRestore') === false) {
            return $m;
        }

        $rs = $m->reload_after_save;
        $m->reload_after_save = false;
        $m->save(['is_deleted'=>false])->unload();
        $m->reload_after_save = $rs;

        $m->hook('afterRestore', [$id]);
        return $m;
    }
}

Implementation of this controller is similar to the one above, however instead of creating softDelete() it overrides the delete() method through a hook. It will still call ‘afterDelete’ to mimic the behavior of regular delete() after the record is marked as deleted and unloaded.

You can still access the deleted records:

$m = new Model_Invoice($db, ['deleted_only'=>true]);
$m->load(10);
$m->restore();

Calling delete() on the model with ‘deleted_only’ property will delete it permanently.

Creating Unique Field

Database can has UNIQUE constraint, but this does work if you use DataSet. For instance, you may be only able to create one ‘Category’ with name ‘Book’, but what if there is a soft-deleted record with same name or record that belongs to another user?

With Agile Data you can create controller that will ensure that certain fields inside your model are unique:

class Controller_UniqueFields {
    use \atk4\core\InitializerTrait {
        init as _init;
    }
    use \atk4\core\TrackableTrait;

    protected $fields = null;

    function init() {
        $this->_init();

        // by default make 'name' unique
        if (!$this->fields) {
            $this->fields = [$this->owner->title_field];
        }

        $this->owner->addHook('beforeSave', $this);
    }

    function beforeSave($m)
    {
        foreach ($this->fields as $field) {
            if ($m->dirty[$field]) {
                $mm = clone $m;
                $mm->addCondition($mm->id_field != $this->id);
                $mm->tryLoadBy($field, $m[$field]);

                if ($mm->loaded()) {
                    throw new \atk4\core\Exception(['Duplicate record exists', 'field'=>$field, 'value'=>$m[$field]]);
                }
            }
        }
    }
}

As expected - when you add a new model the new values are checked against existing records. You can also slightly modify the logic to make addCondition additive if you are verifying for the combination of matched fields.

Creating Many to Many relationship

Depending on the use-case many-to-many relationships can be implemented differently in Agile Data. I will be focusing on the practical approach. My system has “Invoice” and “Payment” document and I’d like to introduce “invoice_payment” that can link both entities together with fields (‘invoice_id’, ‘payment_id’, and ‘amount_closed’). Here is what I need to do:

1. Create Intermediate Entity - InvoicePayment

Create new Model:

class Model_InvoicePayment extends \atk4\data\Model {
    public $table='invoice_payment';
    function init()
    {
        parent::init();
        $this->hasOne('invoice_id', 'Model_Invoice');
        $this->hasOne('payment_id', 'Model_Payment');
        $this->addField('amount_closed');
    }
}

2. Update Invoice and Payment model

Next we need to define reference. Inside Model_Invoice add:

$this->hasMany('InvoicePayment');

$this->hasMany('Payment', [function($m) {
    $p = new Model_Payment($m->persistence);
    $j = $p->join('invoice_payment.payment_id');
    $j->addField('amount_closed');
    $j->hasOne('invoice_id', 'Model_Invoice');
}, 'their_field'=>'invoice_id']);

$this->addHook('beforeDelete',function($m){
    $m->ref('InvoicePayment')->action('delete')->execute();

    // If you have important per-row hooks in InvoicePayment
    // $m->ref('InvoicePayment')->each('delete');
});

You’ll have to do a similar change inside Payment model. The code for ‘$j->’ have to be duplicated until we implement method Join->importModel().

3. How to use

Here are some use-cases. First lets add payment to existing invoice. Obviously we cannot close amount that is bigger than invoice’s total:

$i->ref('Payment')->insert([
    'amount'=>$paid,
    'amount_closed'=> min($paid, $i['total']),
    'payment_code'=>'XYZ'
]);

Having some calculated fields for the invoice is handy. I’m adding total_payments that shows how much amount is closed and amount_due:

// define field to see closed amount on invoice
$this->hasMany('InvoicePayment')
    ->addField('total_payments', ['aggregate'=>'sum', 'field'=>'amount_closed']);
$this->addExpression('amount_due', '[total]-coalesce([total_payments],0)');

Note that I’m using coalesce because without InvoicePayments the aggregate sum will return NULL. Finally let’s build allocation method, that allocates new payment towards a most suitable invoice:

// Add to Model_Payment
function autoAllocate()
{
    $client = $this->ref['client_id'];
    $invoices = $client->ref('Invoice');

    // we are only interested in unpaid invoices
    $invoices->addCondition('amount_due', '>', 0);

    // Prioritize older invoices
    $invoices->setOrder('date');

    while($this['amount_due'] > 0) {

        // See if any invoices match by 'reference';
        $invoices->tryLoadBy('reference', $this['reference']);

        if (!$invoices->loaded()) {

            // otherwise load any unpaid invoice
            $invoices->tryLoadAny();

            if(!$invoices->loaded()) {

                // couldn't load any invoice.
                return;
            }
        }

        // How much we can allocate to this invoice
        $alloc = min($this['amount_due'], $invoices['amount_due'])
        $this->ref('InvoicePayment')->insert(['amount_closed'=>$alloc, 'invoice_id'=>$invoices->id]);

        // Reload ourselves to refresh amount_due
        $this->reload();
    }
}

The method here will prioritize oldest invoices unless it finds the one that has a matching reference. Additionally it will allocate your payment towards multiple invoices. Finally if invoice is partially paid it will only allocate what is due.

Inserting Hierarchical Data

In this example I’ll be building API that allows me to insert multi-model information. Here is usage example:

$invoice->insert([
    'client'=>'Joe Smith',
    'payment'=>[
        'amount'=>15,
        'ref'=>'half upfront',
    ],
    'lines'=>[
        ['descr'=>'Book','qty'=>3, 'price'=>5]
        ['descr'=>'Pencil','qty'=>1, 'price'=>10]
        ['descr'=>'Eraser','qty'=>2, 'price'=>2.5]
    ],
]);

Not only ‘insert’ but ‘set’ and ‘save’ should be able to use those fields for ‘payment’ and ‘lines’, so we need to first define those as ‘never_persist’. If you curious about client lookup by-name, I have explained it in the previous section. Add this into your Invoice Model:

$this->addField('payment',['never_persist'=>true]);
$this->addField('lines',['never_persist'=>true]);

Next both payment and lines need to be added after invoice is actually created, so:

$this->addHook('afterSave', function($m){
    if(isset($m['payment'])) {
        $m->ref('Payment')->insert($m['payment']);
    }

    if(isset($m['lines'])) {
        $m->ref('Line')->import($m['lines']);
    }
});

You should never call save() inside afterSave hook, but if you wish to do some further manipulation, you can reload a clone:

$mm = clone $m;
$mm->reload();
if ($mm['amount_due'] == 0) $mm->save(['status'=>'paid']);

Narrowing Down Existing References

Agile Data allow you to define multiple references between same entities, but sometimes that can be quite useful. Consider adding this inside your Model_Contact:

$this->hasMany('Invoice', 'Model_Invoice');
$this->hasMany('OverdueInvoice', function($m){
    return $m->ref('Invoice')->addCondition('due','<',date('Y-m-d'))
});

This way if you extend your class into ‘Model_Client’ and modify the ‘Invoice’ reference to use different model:

$this->getRef('Invoice')->model = 'Model_Invoice_Sale';

The ‘OverdueInvoice’ reference will be also properly adjusted.