Understanding Expressions. Templates

In previous sections of this documentation expr() was used few times as well as demonstrating how select queries are produced. Each DSQL object has a "template" property which helps query to convert accumulated attributes into a SQL string. This process is called Query Rendering. This is a typical query:

select [options] [fields] [from] [table] [join] [where] [group] [having] [order] [limit]

When query is being rendered (simplest way to render a query is by converting it into a string), the arguments are filled-in by calling methods such as render_options, render_fields, render_from, etc. Those functions will convert the parameters which were previously supplied to the object into a string. If some of the parameters were supplied as objects, those will be recursively rendered.

Writing your own templates

Let's suppose you are willing to extend the query to make it support new query format:

class MyDSQL extends DB_dsql {
    function 
myfunc($arg){
        
$this->args['myarg']=$arg;
        
$this->setTemplate('myfunc([myarg])');
        return 
$this;
    }
    function 
render_myarg(){
        return 
$this->escape($this->args['myarg']);
    }
}

The resulting code above will add ability to call your own stored procedure and specify an argument through a simple interface:

$result $q->myfunc('abc')->getOne();

You may also use subqueries and expresisons easily. When you pass string into escape() function it becomes a paramemtric variable. If you pass another query, it will be rendered and inserted as-is, therefore your code will automatically support:

$q=$this->api->db->dsql('MyDSQL');
$result $q->myfunc($q->expr('2+2'))->getOne();

You should understand that when myfunc() is called, the supplied argument is only stored as a reference.

Standard Templates. Modes.

DSQL implements some of the common queries already. Calling function insert() will automatically apply the "insert" template and will affect the result. Sometimes, however, you would want to know which of those common queries were used. That's why there is a property $mode, which is updated when you call insert(), update() or some other function which changes template.

Defining custom arguments

It might not always be convenient to redefine the DSQL object and add functions in there. There are two ways how you can add support for custom parameters in the template:

$q=$this->api->dsql();
$q->addMethod('render_myargs',function($t){
  return 
$t->escape($t->args['myargs']);
});
$q->addMethod('myfunc',function($t,$args){
  
$t->args['myarg']=$arg[0];
  
$t->setTemplate('myfunc([myarg])');
  return 
$t;
});

$q->myfunc(123)->getOne();

This method uses the standard technique of Agile Toolkit which defines a method on a model-basis. This technique is explained in more details under documentation for AbstractObject class.

Alternatively, you can use setCustom() function which will record the value to put inside template when it's going to be rendered:

$q=$this->add->dsql();
$q->useExpr('myfunc[myarg]')->setCustom('myarg',$q->escape(123));

Example: Building a UNION support

A frequently requested feature to DSQL is a support for "UNION" statement. This can be implemented really easy in DSQL now.

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`