CODEWITHSUNDEEP
phpsqlactiverecordyii
Praxis Ashelin
Praxis Ashelin

Reputation: 5217

SQL to ActiveRecord criteria

I am trying to reproduce the following SQL in an 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 results into 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.

The column should exist though, here are the 2 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?

Upvotes: 4

Views: 193

Answers (2)

Praxis Ashelin
Praxis Ashelin

Reputation: 5217

I've managed to solve the issue as following: Wrap the COALESCE expression in an array, and change the alias to an existing columnname 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)
));

Thank you to Willemn Renzema for helping me with the array part. I'm still not entirely sure why the alias needs to be an existing column name (in this case the error was that price doesn't exist in Table1).

Upvotes: 0

DaSourcerer
DaSourcerer

Reputation: 6606

You will need to use a CDbExpression for the COALESCE() expression:

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

I further believe if table2 has been linked in the relations() method in your Table1 model, the following line should be sufficient:

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

Upvotes: 1

Related Questions