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);
$mydb=$this->add('DB')->connect($dsn);

.

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();
$db
  ->table('user')
  ->where('type','admin')
  ->field('id');
  // configure more
$data = $db
  ->order('created_dts')
  ->field('name,surname')
  ->getAll();
// 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);
}

 

Debugging:

$q->debug();

 

.

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.

$q=$this->api->db->dsql();
$q->table('user')->field('name')->field('surname');

$grid = $this->add('Grid');
$grid->addColumn('text','name');
$grid->addColumn('text','surname');

$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*/
$q->where($q->expr('a=b'));

// 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
$q->where($this->expr('length(password)'),'>',5);

.

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.

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

$q->where(array(
  array($q->expr('len(name)'),'>',5),
  array($q->expr('a=b'))    
  ));// 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) );

 

.

Subqueries

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.

$q
 ->table('author')
 ->field('name')
 ->where('book_id', $q->dsql()->table('book')->where('is_rented','Y') ); 
  /*by default "id" field is used.
   produces: 
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 */

$q
  ->table('author')
  ->field('name')
  ->where(
    $q->dsql()
      ->table('book')
      ->where('author_id', $q->getField('id'))
      ->field('count(*)'),
    '>',5);    
  /* produces:  
select name from author where (select count(*) from book where author_id=author.id)>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.

$q
  ->table('user')
  ->table('address')
  ->where($q->expr('address.user_id=user.id'))

  ->field('name','user')
  ->field('postcode','address');

/* Produces: 
select user.name, postcode.address from user,address where address.user_id=user.id
Similarly to expressions, you may use subqueries.*/
$q
->table('author')
->field('name')
->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 author_id=author.id) total_pages from author */

 

.

.

.

Querying Data From Multiple Tables. Joins

$q->table('user');
$q->join('address');

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.

$q->table('user')->join('address');
/* will produce select user.id from user join address on address.id=user.address_id;*/

/*
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:
*/
$q->table('user')->join('address.user_id');

 // will produce select user.id from user join address on address.user_id=user.id

/*
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: 
*/

$q->table('user')->join('address.code','code');

 // will produce select user.id 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*/

$q->table('user')->join('manager');
$q->join('address.code','manager.code');

// will produce select user.id from user
//   join manager on manager.id=user.manager_id
//   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.

$q->table('user')->join('manager',null,'left');
$q->join('address.code','manager.code');

// will produce select user.id from user
//   left join manager on manager.id=user.manager_id
//   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():

$q->setCustom('q1',$q->dsql()->table('book'))
$q->setCustom('q2',$q->dsql()->table('news'));

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

*/

============

Insert:

$this->api->db->dsql()->table('table')->set($associative_array)->do_insert();
$this->api->db->dsql()->table('table')->set('field1','a')->set('field2','b')->do_insert();

Update:

   $this->api->db->dsql()->table('table1 x')
             ->where('id',$somevariable)
             ->set('col1',$somevalue)
             ->debug()
             ->do_update();

Delete:

  $this->api->db->dsql()->table('table1 x')
             ->where('id',$somevariable)
             ->do_delete();

Other Examples:

    $s=$p->add('Model_Story')->loadData($story);
       $s->set('points', $value);
       $s->update();

 

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

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

 

  $p=$this->add('Model_Purchase');
    $p->addCondition('user_id','in',$m->dsql()->field('id'));
    $p->deleteAll();

============================

$model->load($id);

$model->tryLoad($id)

$model->loadAny()

$model->tryLoadAny()

$model->loadBy($field,$condition,$value);
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.

$model->tryLoadBy($field,$condition,$value);

=====================

Another Examples:

$model->addCondition(‘gender’,’M’);
$model->loadAny();
$model->set(‘gender’,’F’);
$model->save();  
//

$boy=$this->add(‘Model_Person’)->addCondition(‘gender’,’M’);
$girl=$this->add(‘Model_Person’)->addCondition(‘gender’,’F’);

$boy->loadAny();
$boy->saveAs($girl);
//

$model->saveAndUnload();
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;

$model->loadBy('age','>','25');
$model->loadBy($model->dsql->expr('expire_dts>now()'));

//
//getBy($field,[$cond],$value)

/*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);


//getRows(array)
/*
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){
    $book['sale']++;
    $book->save();
}

…..

Leave a Reply

Your email address will not be published. Required fields are marked *