Agile Toolkit All In One Digest – Part 1

At this post we are going to provide an abstract of agile toolkit documents…



Just extract the files!



Agile Toolkit uses an array for defining configuration.



The method


is used to to retrieve config settings. The second argument to this function is a default value, and it’s used extensively throughout Agile Toolkit add-ons and components.

Copy the config-default.php and name it as config.php.

By default Agile Toolkit will read the config.php and config-defaults.php files. The settings in config.php will override the settings in config-defaults.php.

The file config-distrib.php is never loaded. It is a “template” that you can use to make your own copy of config.php.

Continue reading “Agile Toolkit All In One Digest – Part 1”

Agile Toolkit Hooks ad Controllers Digest

There are the following hooks available for the model (at least):

  • beforeLoad($model, $query) – called before loading SQL query is executed. You have a chance to modify that query. This is called for both model->load() and for iterating through model with foreach($model). This hook is great for applying extra options to your SQL query.
  • afterLoad($model) – called after data have been loaded from SQL. You can now access $model->get() and the model will appear to be loaded. Called for both model->load() and iterating. This hook is great for performing data manipulation and normalization.
  • beforeSave($model) – called when $model->save() is called. This is called inside SQL transaction, so database changes you perform here will be rolled back if save would be unsuccessful. This hook is great for performing data modification before it’s been saved. You can check $model->loaded() to see if a new record is being stored or updated
  • beforeInsert($model, $query) – called when inserting new data and after the insert query is being formed. That query is passed as 2nd argument. This hook is great for changing insert query options.
  • afterInsert($model,$id) – called after insertion method is performed successfully, but before model is re-loaded. You can break out of this hook and return a substitute model. Great for overriding how model is reloaded after insert.
  • beforeModify($model,$query) – called before update SQL query is executed. This hook is great for changing update query options.
  • afterModify($model) – called after SQL query is executed but before reloading has taken place. Note that if you access set() / get() here it will be reloaded by a subsequental reload.
  • afterSave($model) – called after model have been successfully reloaded. This is the last hook to be executed before SQL transaction is finished with commit. Please note taht beforeLoad / afterLoad will also be called during the reloading of a model. This hook is great for hiding some fields from a model after they are being saved such as wiping your password field.

Few more recommendation on query use.

  • to perform additional validation use beforeSave().
  • to measure speed of your query use beforeInsert / afterInsert and beforeModify and afterModify respectively.
  • beforeLoad will be called once per query, but afterLoad may be called several times when iterating through results.
  • apply SQL options in beforeLoad, beforeInsert and beforeModify
  • if you want to divert the query to a different database connection beforeLoad, beforeInsert and beforeModify are good place, but you must enhance DSQL to support switching before databsae handles. If this is what you need, please discuss on Agile Toolkit Development Forum.


class Model_Book extends Model_Table {
  function init(){


  function beforeSave(){
    // manually update some fields before saving. This is to create a indexable field for full-text search
    $this['book_search_field'] = $this['title'].' '.$this['descr'].' '.$this['author_name'];

    // let's also perform some validation
    if(strlen($this['book_name']<10))throw $this->exception('Name of the book is too short');

    // normalization will modify field to match some internal rules
    $this['book_url']=$this['book_url'] ?: preg_replace('/[^a-zA-Z0-9]/','-',trim($this['name']));
  function afterLoad(){
    $this['name'] = $this->api->_($this['name']); // wraps name through localization function



Agile Toolkit Database Digest

Connecting to Database

Agile Toolkit API has a function dbConnect() which will automatically read DB configuration from your configuration file and initialize connection. When connection is created, the connection object is accessible through through $api->db property. If you wish, you can create connections to other databases by calling:

$dsn=array('mysql:host=localhost;dbname=testdb', $username, $password, $options);


Creating Query Object

DSQL objects are created by calling dsql() function of either DB object or other DSQL object. This function always returns empty query.

// use default connection
$q = $this->api->db->dsql();
// or
$q = $mydb->dsql();

You may also call $model->dsql() which will return initialized Query Object with your particular Model settings.


Query Config:

$db = $this->api->db->dsql();
  // configure more
$data = $db
// Produces: $data=array(
//   array('id'=>1, 'name'=>'John', 'surname'=>'Smith'),
//   array('id'=>2, 'name'=>'Joe', 'surname'=>'Blogs')
// );
foreach($this->api->db->dsql()->expr('show tables') as $row){
  $table_name = pop($row);
  $this->add('Text')->set('Table: '.$table_name);






Fetching ways in DSQL:

$data = $q->get();    /* same as getAll();*/
$data = $q->getAll();   /* returns all data as array of hashes, array() if query produced no results.*/
$data = $q->getRow();     /* returns only first row of data, null if query produced no results.*/
$data = $q->getOne();     /* returns only single value, null if query produced no results or result was NULL.*/

while($row = $q->fetch()){
  /* Will loop through results fetching one row at a time.
 You can access your data through $row['fieldname'];*/
foreach($q as $row){
  // $row is associative array.

//PDO statement...
$q->execute();    /*Prepares and Executes statement*/
$stmt = $q->stmt;


Lister class and all derived classes (CompleteLister, Grid) accept Iterate-able classes through setSource() method.


$grid = $this->add('Grid');

$grid->setSource( $q );   // Associate Grid with data-source.




Adding condition to  DSQL

$q->where('id',1);        // where id=:a    'a'=>1
$q->where('id>',1);       // where id>:a    'a'=>1
$q->where('id!=',1);      // where id!=:a   'a'=>1
$q->where('id like',1);   // where id like :a   'a'=>1
$q->where('id in',array(1,2));      // where id in(:a,:b)   'a'=>1, 'b'=>2

$q->where('id',null);       /* where id is NULL*/
$q->where('id is',null);    /* where id is NULL*/
$q->where('id!=',null);     /* where id is NOT NULL*/
$q->where('id is not',null); /*where id is NOT NULL*/

/*Using with Expressions: expr()
 Single argument mode*/

// Using operator with the first argument
$q->where('date>',$q->expr('DATE_SUB(CURDATE(), INTERVAL 2 MONTH)');

/* Expression may contain parameters. 
Unlike where('id',1) this will not use equation operator*/
$q->where('age',$q->expr('between 5 and 10'));

// both arguments may be expressions
$q->where($this->expr('length(password)'),$q->expr('between 3 and 10'));

// Alternative way to specify parameter


AND conditions: where(..)->where()

Calling where() multiple times will require all of the conditions to be met. Using “AND” operator.


OR conditions: where(array)

Callng where() with a single array argument will use OR to join those conditions. The same principles apply on the array as no the actual where() call. You can even specify arrays recursively.

  ));/* where (id=:a or id=:b) array('a'=>1, 'b'=>2)*/

  ));// where (len(name)>:a or a=b) array('a'=>5)

There is alternative way to use OR conditions. Use whichever you like more. on() method relies on expr() to produce a new query. (I think this way is better!)

$q->where( $q->or()->where('a',1)->where('b>',5) );




You may use $q->dsql() as a quick way to produce sub-queries. Calling this method will create a new DSQL object, which you can use similarly as expression.

 ->where('book_id', $q->dsql()->table('book')->where('is_rented','Y') ); 
  /*by default "id" field is used.
select name from author where book_id in (select id from book where is_rented=:a)  array('a'=>'Y')
  Note: This is quite ineffective way for listing all authors who's books are rented */

      ->where('author_id', $q->getField('id'))
  /* produces:  
select name from author where (select count(*) from book where>5
   Displays names of authors who have more than 5 books. */


The second argument can be used to specify which table field is queried from. That’s handy when you are joining tables.



/* Produces: 
select, postcode.address from user,address where
Similarly to expressions, you may use subqueries.*/
->field( $q->dsql() ->table('book')->where('author_id',$q->getField('id')) ->field($q->expr('sum(pages)')) , 'total_pages');
/* Produces: select name,(select sum(pages) from book where total_pages from author */





Querying Data From Multiple Tables. Joins


echo $q->getField('name');   // will output `user`.`name`
echo $q->getField('name','address');   // will output `address`.`name`

The method join() has several ways to call it. The simplest is by specifying only one string argument.

In this case the table is joined and the table_id from main_table will be used in the “ON” condition.

/* will produce select from user join address on;*/

By default the "id" of JOINED table is linked with the table_id in the main table. 
This can be changed, however. The "id" of JOINED table can be set to a different 
field if you specify that field with a dot when joining:

 // will produce select from user join address on

As you noticed, the field from the main_table was changed to "id". 
If we want to specify that field manually, we can use second argument in the join() method: 


 // will produce select from user join address on address.code=user.code

/*Or we can also specify the table which should participate with the join if we use 
the dot in the 2nd argument also*/


// will produce select from user
//   join manager on
//   join address.code=manager.code


Specifying join type

The third argument in the “join” method can be used to specify the type of join. You can use “left”, “right” or “outer” or whatever join type is supported by your SQL. By default the join type is not specified.


// will produce select from user
//   left join manager on
//   join address.code=manager.code


Building a UNION support

First you would need to use a new template for union like this:

$q=$this->useExpr('[q1] UNION [q2]');

Next you need to assign the arguments with setCustom():


/* Result:
select * from `book` UNION select * from `news` 






   $this->api->db->dsql()->table('table1 x')


  $this->api->db->dsql()->table('table1 x')

Other Examples:

       $s->set('points', $value);


$authors = $this->add('Model_Author'); // selects ALL authors

foreach($authors as $junk){
  echo $authors['name'].': sold '.$authors['books_sold']."\n";








This have received a significant flexibility in the arguments. Actually it relies on addCondition(), but it’s still powerful.

 loadBy($model->dsql->expr(‘rand() > 0.2’) );
 loadBy($model->dsql->expr(‘rand()’), ‘>‘, $_GET[‘id’] ); // safe against injeciotn
 loadBy(‘calc_field’, 200); // using calculated field automatically switches to “having” clause.



Another Examples:




This will save model into the database but will not load data back. The model loaded() will return false. Slightly better if you need performance.

$model->saveLater(); Will not save right away, but will save when model is being destroyed by Garbage Collector;



/*This similar syntax will return array of hashes produced the model's select.*/
$data = $model->getBy($model->dsql->expr('expire_dts>now()'));
echo json_encode($data);

This is similar to getBy but it allows to define which fields you are willing to retrieve:*/
$data = $model->getRows(array('id','name'))
echo json_encode($data);

Iterating Through Entities

foreach($this->add('Model_Book') as $book){