Basic Usage of Dynamic SQL Queries

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

Alternatively you may pass PEAR::DB-compatible DSN in format "mysql://user:password@localhost/testdb" or a configuration variable path (dsn). Connections in Agile Toolkit are lazy — they may not be physically created unless you execute a query.

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.

If you create DSQL query before connecting to database, then DSQL will use generic DB_dsql. Always try to execute connect() before creating query objects.

Configuring Query

There are number of methods which can be used to "configure" the query. You can call those methods several times and in any order. You can even call them after you already executed query once. All those methods will return $this therefore you can safely chain them:

$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')
// );
//  

All the functions called will be explained in the further sections, although most of them must be simple to understand from just names.

Below is a different example, which uses a custom expression and fetches data on-demand.

foreach($this->api->db->dsql()->expr('show tables') as $row){
  
$table_name pop($row);
  
$this->add('Text')->set('Table: '.$table_name);
}

DSQL implements only some PDO fetching modes/features for simplicity, although you can access PDO object through $q->stmt.

Specifying table

Calling table('tbl') is the only requirement before you execute your query. You may specify a second argument to table($table, $alias) which will place an alias for the table and all the fields.

Debugging

dsql has a method debug() which will "echo" queries as they are being produced.

$q=$this->api->db->dsql();
$q->table('user');
$q->debug();
$q->field('name');
$data $q->get();    // will output debugging information

DSQl may not re-generate query once it's generated for purposes of performance.