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){