Expressions
- class Atk4\Data\Model
You already know that you can define fields inside your Model with addField. While a regular field maps to physical field inside your database, sometimes you want to do something different - execute expression or function inside SQL and use result as an output.
Expressions solve this problem by adding a read-only field to your model that corresponds to an expression:
- Atk4\Data\Model::addExpression($name, $seed)
Example will calculate “total_gross” by adding up values for “net” and “vat”:
$m = new Model_Invoice($db);
$m->addField('total_net');
$m->addField('total_vat');
$m->addExpression('total_gross', ['expr' => '[total_net] + [total_vat]']);
$m = $m->load(1);
echo $m->get('total_gross');
The query using during load() will look like this:
select
`id`, `total_net`, `total_vat`,
(`total_net` + `total_vat`) `total_gross`
from `invoice`
Defining Expression
The simplest format to define expression is by simply passing a string. The argument is executed through Model::expr() which automatically substitutes values for the other fields including other expressions.
There are other ways how you can specify expression:
$m->addExpression('total_gross', [
'expr' => $m->expr('[total_net] + [total_vat] + [fee]', ['fee' => $fee]),
]);
This format allow you to supply additional parameters inside expression. You should always use parameters instead of appending values inside your expression string (for safety)
You can also use expressions to pass a select action for a specific field:
No-table Model Expression
Agile Data allows you to define a model without table. While this may have no purpose initially, it does come in handy in some cases, when you need to unite a few statistical queries. Let’s start by looking a at a very basic example:
$m = new Model($db, ['table' => false]);
$m->addExpression('now', ['expr' => 'now()']);
$m = $m->loadAny();
echo $m->get('now');
In this example the query will look like this:
select (1) `id`, (now()) `now` limit 1
so that $m->getId()
will always be 1 which will make it a model that you can
actually use consistently throughout the system. The real benefit from this
can be gained when you need to pull various statistical values from your
database at once:
$m = new Model($db, ['table' => false]);
$m->addExpression('total_orders', ['expr' => (new Model_Order($db))->action('count')]);
$m->addExpression('total_payments', ['expr' => (new Model_Payment($db))->action('count')]);
$m->addExpression('total_received', ['expr' => (new Model_Payment($db))->action('fx0', ['sum', 'amount'])]);
$data = $m->loadOne()->get();
Of course you can also use a DSQL for this:
$q = $db->dsql();
$q->field(new Model_Order($db)->action('count'), 'total_orders');
$q->field(new Model_Payment($db)->action('count'), 'total_orders');
$q->field(new Model_Payment($db)->action('fx0', ['sum', 'amount']), 'total_received');
$data = $q->getRow();
You can decide for yourself based on circumstances.
Expression Callback
You can use a callback method when defining expression:
$m->addExpression('total_gross', ['expr' => function (Model $m, Expression $q) {
return '[total_net] + [total_vat]';
}, 'type' => 'float']);
Model Reloading after Save
When you add SQL Expressions into your model, that means that some of the fields might be out of sync and you might need your SQL to recalculate those expressions.
To simplify your life, Agile Data implements smart model reloading. Consider the following model:
class Model_Math extends \Atk4\Data\Model
{
public $table = 'math';
protected function init(): void
{
parent::init();
$this->addField('a');
$this->addField('b');
$this->addExpression('sum', ['expr' => '[a] + [b]']);
}
}
$m = new Model_Math($db);
$m->set('a', 4);
$m->set('b', 6);
$m->save();
echo $m->get('sum');
When $m->save() is executed, Agile Data will perform reloading of the model. This is to ensure that expression ‘sum’ would be re-calculated for the values of 4 and 6 so the final line will output a desired result - 10;
Reload after save will only be executed if you have defined any expressions inside your model, however you can affect this behavior:
$m = new Model_Math($db, ['reloadAfterSave' => false]);
$m->set('a', 4);
$m->set('b', 6);
$m->save();
echo $m->get('sum'); // outputs null
$m->reload();
echo $m->get('sum'); // outputs 10
Now it requires an explicit reload for your model to fetch the result. There is another scenario when your database defines default fields:
alter table math change b b int default 10;
Then try the following code:
class Model_Math extends \Atk4\Data\Model
{
public $table = 'math';
protected function init(): void
{
parent::init();
$this->addField('a');
$this->addField('b');
}
}
$m = new Model_Math($db);
$m->set('a', 4);
$m->save();
echo $m->get('a')+$m->get('b');
This will output 4, because model didn’t reload itself due to lack of any expressions. This time you can explicitly enable reload after save:
$m = new Model_Math($db, ['reloadAfterSave' => true]);
$m->set('a', 4);
$m->save();
echo $m->get('a')+$m->get('b'); // outputs 14
Note
If your model is using reloadAfterSave, but you wish to insert
data without additional query - use Model::insert()
or
Model::import()
.