SQL Criteria for ActiveRecord

Question

I am trying to reproduce the following SQL in ActiveRecord criteria:

SELECT COALESCE(price, standardprice) AS price
FROM table1
LEFT JOIN table2
ON (pkTable1= fkTable1)
WHERE pkTable1= 1

So far I have the following:

$price = Table1::model()->find(array(
    "select" => "COALESCE(price, standardprice) AS price",
    'with' => array(
        'table2' => array(
            'joinType' => 'LEFT JOIN',
            'on' => 'pkTable1= fkTable1',
        )
    ),
    'condition' => 'pkTable1=:item_id',
    'params' => array(':item_id' => 1)
));

But this leads to the following error: 'Active record "Table1" is trying to select an invalid column "COALESCE(price". Note, the column must exist in the table or be an expression with alias.

A column must exist, although there are two table structures:

Table1

pkTable1        int(11) - Primary key
standardprice   decimal(11,2)
name            varchar(255) //not important here
category        varchar(255) //not important here

Table2

pkTable2        int(11) - Primary key //not important here
fkType          int(11) - Foreign key //not important here
fkTable1        int(11) - Foreign key, linking to Table1
price           decimal(11,2)

What exactly am I doing wrong?

Answer 1

I managed to solve the problem as follows: Wrap the COALESCE expression in an array and change the alias to an existing column name in the table.

$price = Table1::model()->find(array(
    "select" => array("COALESCE(price, standardprice) AS standardprice"),
    'with' => array(
        'table2' => array(
            'joinType' => 'LEFT JOIN',
            'on' => 'pkTable1= fkTable1',
        )
    ),
    'condition' => 'pkTable1=:item_id',
    'params' => array(':item_id' => 1)
));

Willemn Renzema , . , ( , price 1).

Answer 2

CDbExpression COALESCE():

$price=Table1::model()->find(array(
    'select'=>array(
        new CDbExpression('COALESCE(price, standardprice) AS price'),
    ),
    'with' => array(
        'table2' => array(
            'joinType'=>'LEFT JOIN',
            'on'=>'pkTable1=fkTable1',
        ),
    ),
    'condition'=>'pkTable1=:item_id',
    'params'=>array(':item_id'=>1)
));

, table2 relations() Table1, :

'with'=>array('table2'),