Sub-Queries, Expressions and Object Management

When you execute expr() it will create a new query with specified expression and return the reference to you. This is used in some cases such as "or" argument. Calling $q->orExpr() will return an empty query, with a special template which is then used to render sequence of OR-connected conditions.

Sub-queries does not need to be produced from the same object. Let's assume that you have a method which returns query listing all bonuses of particular employee

function getBonus($employee_id){
    
$q $this->dsql()->table('bonuses');
    
$q->field($q->expr('sum(bonus)'));
    
$q->where('id',$employee_id);
    return 
$q;
}

An obvious usage of such a function would be:

echo getBonus(123)->getOne();

It is however possible to use the function to generate a sub-query without any modifications:

$q $this->dsql()->table('employee');
$q->field('name,surname');
$q->field(getBonus($q->getField('id')),'bonuses');

foreach(
$q as $row){
    echo 
$row['name'].': '.$row['bonuses']."\n";
}

When you are using this inside Models it becomes extremely powerful tool:

// inside Model_PaidBonus

class Model_PaidBonuss extends Model_Bonus {
    function 
init(){
        
parent::init();
        
$this->addCondition('is_paid',true);
    }
    function 
getBonus($employee_id){
        
$q=$this->dsql();
        return 
$q
            
->field($q->expr('sum(salary)'))
            ->
where('id',$employee_id)
            ;
    }
}

Not only the function becomes universal for individual requests or can be used as sub-query, it also relies on SQL produced by model which would include the right condition to filter by a boolean field is_paid.

DSQL is irreplaceable when used as a glue between model selects and can be extensively used to increase your database query performance without loosing flexibility of your business level framework.