refSQL() cannot handle conditions based on expressions? (database query error "no such column")

Question

This is a follow up from this question: Agile toolkit - access calculated fields (expressions) from dsql? . I'm not sure if this is a bug or if I'm just missing something.

Here's my current code:

class Model_UsedItem extends Model_Table {
public $table='usedItems';

function init() {
    parent::init();
    $this->hasOne('Case', 'case_id', 'id');
    $this->hasOne('Item', 'item_id', 'description');
    $i = $this->join('Items', 'item_id');
    $i->addField('unitCost')->hidden(true);
    $this->addField('quantity')
    $this->addExpression('subTotal')->set('quantity * unitCost')->type('money');
}

class Model_Case extends Model_Table {
public $table='cases';

function init(){
    parent::init();
    $this->getElement('id')->system(false);
    $this->addField('date')->type('date');
    $this->hasMany('UsedItem', 'case_id');
    $this->addExpression('Total')->set($this->refSQL('UsedItem')->sum('subTotal'))->type('money');
}

This works fine. Now say I want to modify Model_UsedItem so that it only selects rows where subTotal < 10 - simple: $this->addCondition('subTotal','<',10);. Now if I load Model_UsedItem into a grid, I see just rows with the condition.

When I go to load Model_Case into a grid, I expected to see the Total column consist of sum(subTotal) where subTotal < '10'.

Instead it throws a Database Query Failed error: "no such column subTotal". debug() shows that it tried to do this:

select `id`,`date`,(select sum(quantity * unitCost) from `usedItems` inner join `Items` as `_I` on `_I`.`id` = `usedItems`.`item_id` where `subTotal` < "10" and `usedItems`.`case_id` = `cases`.`id` ) `Total`

I'm able to add conditions based on real fields, so I'm pretty sure this just needs to be

select `id`,`date`,(select sum(`subTotal`) from (select (quantity * unitCost) `subTotal` from `usedItems` inner join `Items` as `_I` on `_I`.`id` = `usedItems`.`item_id` where `usedItems`.`case_id` = `cases`.`id` and `subTotal` < 10)) `Total`

but how do I convince agile toolkit to do that?

EDIT: @DarkSide was correct, my code does read ('quantity * unitCost'), I revised above.

Answer 1

It looks that it's some kind of bug (or not implemented feature) in ATK.

You got SQL:

select `id`,`date`,(select sum(quantity * unitCost) from `usedItems` inner join `Items` as `_I` on `_I`.`id` = `usedItems`.`item_id` where `subTotal` < "10" and `usedItems`.`case_id` = `cases`.`id` ) `Total`

but in this case it should be (quantity * unitCost) instead of subTotal in WHERE part like this:

select `id`,`date`,(select sum(quantity * unitCost) from `usedItems` inner join `Items` as `_I` on `_I`.`id` = `usedItems`.`item_id` where (quantity * unitCost) < "10" and `usedItems`.`case_id` = `cases`.`id` ) `Total`

I wonder how debug() of Model_UsedItems looks like when you have condition set. Can you tell me? Do it have (quantity * unitCost)<10 or subTotal<10?

---

EDIT:

I just created your testcase and tried it with about 3 months old ATK 4.2.5 version and also current one from github. Everything looks OK.

Complete test case (name it a.php and put it in /page folder):

class Model_UsedItem extends Model_Table {
    public $table='usedItems';
    function init() {
        parent::init();
        $this->hasOne('Case', 'case_id', 'id');
        $i = $this->join('Items', 'item_id');
        $i->addField('unitCost');
        $this->addField('quantity');
        $this->addExpression('subTotal')->set('quantity * unitCost');
        $this->addCondition('subTotal','<',10);
    }
}
class Model_Case extends Model_Table {
    public $table='cases';
    function init(){
        parent::init();
        $this->addField('date');
        $this->hasMany('UsedItem', 'case_id');
        $this->addExpression('Total')->set($this->refSQL('UsedItem')->sum('subTotal'));
    }
}
class page_a extends Page {
    function init(){
        parent::init();

        $m = $this->add('Model_Case')->debug();
        $this->add('Grid')->setModel($m);
    }
}

Produced SQL:

select `date`,(select sum(quantity * unitCost) `subTotal` from `usedItems` inner join `Items` as `_I` on `_I`.`id` = `usedItems`.`item_id` where quantity * unitCost < 10 and `usedItems`.`case_id` = `cases`.`id` ) `Total`,`id` from `cases`

As you can see it has subTotal alias and doesn't use alias (use full expression) in where part because aliases in SQL SELECT should be used only in having or order by (after select executes and gets resultset) not in where.