Model Definition

Models in Agile Toolkit are defined dynamically inside their init() method by calling addField() multiple times.

Using Expressions with a single table

One Table Example

class Model_ExpressionTest extends Model_Table {
public $table='movie';
	function init(){
    	parent::init();

    	$this->addField('name');
    	$this->addField('year');

    	$this->addExpression('age')->set('year(now())-year');

    	$this->addExpression('title')->set(function($m,$q){
    		return $q->expr('concat([f1]," (",[f2],")")')
    		->setCustom('f1',$m->getElement('name'))
    		->setCustom('f2',$m->getElement('age'))
    		;
    	});
   	}
}

$m=$this->add('Model_ExpressionTest');
$page->add('Grid')->setModel($m);

Demo

Name	Year	Age	Title
V for Vendetta	2006	7	V for Vendetta (7)
The Matrix	1999	14	The Matrix (14)
WALL-E	2008	5	WALL-E (5)

Agile Toolkit will automatically determines type of your fields and uses them in a query:

select `name`,`year`,year(now())-year `age`,concat(`movie`.`name`," (",year(now())-year,")") `title`,`id` from `movie`

The next example uses a hasOne relation which indirectly creates a calculated field "movie". This field now is used instead of the "name" from previous example and although it does affect the query, the model definition code looks very consistent to what you had before.

Using Expressions with related table

Multi Table Example

Comment Link Copy

class Model_ExpressionTest2 extends Model_Table {
public $table='dvd';
	function init(){
    	parent::init();

    	$this->hasOne('ExpressionTest','movie_id');

    	$q=$this->join('movie');
    	$q->addField('year');

    	$this->addExpression('age')->set(
    		$this->dsql()->expr('year(now())-[f1]')
    			->setCustom('f1',$this->getElement('year'))
    		);

    	$this->addExpression('title')->set(function($m,$q){
    		return $q->expr('concat([f1]," (",[f2],")")')
    		->setCustom('f1',$m->getElement('movie'))
    		->setCustom('f2',$m->getElement('age'))
    		;
    	});
   	}
}

$m=$this->add('Model_ExpressionTest2');
$page->add('Grid')->setModel($m);

Demo

Movie	Year	Age	Title
V for Vendetta	2006	7	V for Vendetta (7)
V for Vendetta	2006	7	V for Vendetta (7)
The Matrix	1999	14	The Matrix (14)

select (select `movie`.`name` from `movie` where `dvd`.`movie_id` = `movie`.`id` ) `movie`,year(now())-`_m`.`year` `age`,`_m`.`year`,concat((select `movie`.`name` from `movie` where `dvd`.`movie_id` = `movie`.`id` )," (",year(now())-`_m`.`year`,")") `title`,`dvd`.`id`,`dvd`.`movie_id` `_m` from `dvd` inner join `movie` as `_m` on `_m`.`id` = `dvd`.`movie_id`

One other note is that I had to add "join" with the movie table in order to be able to acquire the year of the movie. Expression field "title" have used a proper prefix to adress the age which is now defined in a related table and not the main table.

Disqus