Advanced Topics

Agile Data allow you to implement various tricks.

SubTypes

Disjoint subtypes is a concept where you give your database just a little bit of OOP by allowing to extend addional types without duplicating columns. For example, if you are implementing “Account” and “Transaction” models. You may want to have multiple transaction types. Some of those types would even require additional fields. The pattern suggest you should add a new table “transaction_transfer” and store extra fields there. In your code:

class Transaction_Transfer extends Transaction
{
    protected function init(): void
    {
        parent::init();

        $j = $this->join('transaction_transfer.transaction_id');
        $j->addField('destination_account');
    }
}

As you implement single Account and multiple Transaction types, you want to relate both:

$account->hasMany('Transactions', ['model' => [Transaction::class]]);

There are however two difficulties here:

  1. sometimes you want to operate with specific sub-type.
  2. when iterating, you want to have appropriate class, not Transaction()

Best practice for specifying relation type

Although there is no magic behind it, I recommend that you use the following code pattern when dealing with multiple types:

$account->hasMany('Transactions', ['model' => [Transaction::class]]);
$account->hasMany('Transactions:Deposit', ['model' => [Transaction\Deposit::class]]);
$account->hasMany('Transactions:Transfer', ['model' => [Transaction\Transfer::class]]);

You can then use type-specific reference:

$account->ref('Transaction:Deposit')->insert(['amount' => 10]);

and the code would be clean. If you introduce new type, you would have to add extra line to your “Account” model, but it will not be impacting anything, so that should be pretty safe.

Type substitution on loading

Another technique is for ATK Data to replace your object when data is being loaded. You can treat “Transaction” class as a “shim”:

$obj = $account->ref('Transactions')->load(123);

Normally $obj would be instance of Transaction class, however we want this class to be selected based on transaction type. Therefore a more broad record for ‘Transaction’ should be loaded first and then, if necessary, replaced with the correct class transparently, so that the code above would work without a change.

Another scenario which could benefit by type substitution would be:

foreach ($account->ref('Transactions') as $tr) {
    echo get_class($tr) . "\n";
}

ATK Data allow class substitution during load and iteration by breaking “afterLoad” hook. Place the following inside Transaction::init():

$this->onHookShort(Model::HOOK_AFTER_LOAD, function () {
    if (get_class($this) != $this->getClassName()) {
        $cl = $this->getClassName();
        $m = new $cl($this->getModel()->getPersistence());
        $m = $m->load($this->getId());

        $this->breakHook($m);
    }
});

You would need to implement method “getClassName” which would return DESIRED class of the record. Finally to help with performance, you can implement a switch:

public $typeSubstitution = false;

...

protected function init(): void
{
    ...

    if ($this->typeSubstitution) {
        $this->onHook(Model::HOOK_AFTER_LOAD,
            ...
        )
    }
}

Now, every time you iterate (or load) you can decide if you want to invoke type substitution:

foreach ($account->ref('Transactions', ['typeSubstitution' => true]) as $tr) {
    $tr->verify(); // verify() method can be overloaded!
}


// however, for export, we don't need expensive substitution
$transactionData = $account->ref('Transaction')->export();

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 ControllerAudit
{
    use \Atk4\Core\InitializerTrait {
        init as private _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 ControllerAudit()) 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:

protected function init(): void
{
    $this->_init();

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

    $this->getOwner()->addField('created_dts', ['type' => 'datetime', 'default' => new \DateTime()]);

    $this->getOwner()->hasOne('created_by_user_id', 'User');
    if (isset($this->getApp()->user) && $this->getApp()->user->isLoaded()) {
        $this->getOwner()->getField('created_by_user_id')->default = $this->getApp()->user->getId();
    }

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

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

    $this->getOwner()->onHook(Model::HOOK_BEFORE_UPDATE, function (Model $m, array $data) {
        if (isset($this->getApp()->user) && $this->getApp()->user->isLoaded()) {
            $data['updated_by'] = $this->getApp()->user->getId();
        }
        $data['updated_dts'] = new \DateTime();
    });
}

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 ControllerSoftDelete
{
    use \Atk4\Core\InitializerTrait {
        init as private _init;
    }
    use \Atk4\Core\TrackableTrait;

    protected function init(): void
    {
        $this->_init();

        if (property_exists($this->getOwner(), 'no_soft_delete')) {
            return;
        }

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

        if (property_exists($this->getOwner(), 'deleted_only') && $this->getOwner()->deleted_only) {
            $this->getOwner()->addCondition('is_deleted', true);
            $this->getOwner()->addMethod('restore', \Closure::fromCallable([$this, 'restore']));
        } else {
            $this->getOwner()->addCondition('is_deleted', false);
            $this->getOwner()->addMethod('softDelete', \Closure::fromCallable([$this, 'softDelete']));
        }
    }

    public function softDelete(Model $entity)
    {
        $entity->assertIsLoaded();

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

        $entity->saveAndUnload(['is_deleted' => true]);

        $entity->hook('afterSoftDelete', [$id]);

        return $entity;
    }

    public function restore(Model $entity)
    {
        $entity->assertIsLoaded();

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

        $entity->saveAndUnload(['is_deleted' => false]);

        $entity->hook('afterRestore', [$id]);

        return $entity;
    }
}

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 = $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 reloadAfterSave 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 = $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 ControllerSoftDelete2 extends ControllerSoftDelete
{
    protected function init(): void
    {
        parent::init();

        $this->getOwner()->onHook(Model::HOOK_BEFORE_DELETE, \Closure::fromCallable([$this, 'softDelete']), null, 100);
    }

    public function softDelete(Model $entity)
    {
        parent::softDelete();

        $entity->hook(Model::HOOK_AFTER_DELETE);

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

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 = $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 ControllerUniqueFields
{
    use \Atk4\Core\InitializerTrait {
        init as private _init;
    }
    use \Atk4\Core\TrackableTrait;

    protected $fields = null;

    protected function init(): void
    {
        $this->_init();

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

        $this->getOwner()->onHook(Model::HOOK_BEFORE_SAVE, \Closure::fromCallable([$this, 'beforeSave']));
    }

    protected function beforeSave(Model $entity)
    {
        foreach ($this->fields as $field) {
            if ($entity->getDirtyRef()[$field]) {
                $modelCloned = clone $entity->getModel();
                $modelCloned->addCondition($entity->idField != $this->id);
                $entityCloned = $modelCloned->tryLoadBy($field, $entity->get($field));

                if ($entityCloned !== null) {
                    throw (new \Atk4\Data\Exception('Duplicate record exists'))
                        ->addMoreInfo('field', $field)
                        ->addMoreInfo('value', $entity->get($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.

Using WITH cursors

Many SQL database engines support defining WITH cursors to use in select, update and even delete statements.

addCteModel(string $name, Model $model, bool $recursive = false)

Agile toolkit data models also support these cursors. Usage is like this:

$invoices = new Invoice();

$contacts = new Contact(); $contacts->addCteModel(‘inv’, $invoices); $contacts->join(‘inv.cid’);

with
    `inv` as (select `contact_id`, `ref_no`, `total_net` from `invoice`)
select
    *
from `contact`
    join `inv` on `inv`.`contact_id`=`contact`.`id`

Note

Supported since MySQL 8.x, MariaDB supported it earlier.

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';

    protected function init(): void
    {
        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', ['model' => function (self $m) {
    $p = new Model_Payment($m->getPersistence());
    $j = $p->join('invoice_payment.payment_id');
    $j->addField('amount_closed');
    $j->hasOne('invoice_id', 'Model_Invoice');
}, 'theirField' => 'invoice_id']);

$this->onHookShort(Model::HOOK_BEFORE_DELETE, function () {
    foreach ($this->ref('InvoicePayment') as $payment) {
        $payment->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->get('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', ['expr' => '[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
public 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->get('amount_due') > 0) {
        // see if any invoices match by 'reference'
        $invoice = $invoices->tryLoadBy('reference', $this->get('reference'));

        if ($invoice === null) {
            // otherwise load any unpaid invoice
            $invoice = $invoices->tryLoadAny();

            if ($invoice === null) {
                // couldn't load any invoice
                return;
            }
        }

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

        // 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 ‘neverPersist’. 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', ['neverPersist' => true]);
$this->addField('lines', ['neverPersist' => true]);

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

$this->onHookShort(Model::HOOK_AFTER_SAVE, function (bool $isUpdate) {
    if ($this->_isset('payment')) {
        $this->ref('Payment')->insert($this->get('payment'));
    }

    if ($this->_isset('lines')) {
        $this->ref('Line')->import($this->get('lines'));
    }
});

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

$entityCloned = clone $entity;
$entityCloned->reload();
if ($entityCloned->get('amount_due') == 0) {
    $entityCloned->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', ['model' => function (self $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->getReference('Invoice')->model = 'Model_Invoice_Sale';

The ‘OverdueInvoice’ reference will be also properly adjusted.