I have the following query:
$result = Table1::model()->findAll(array(
'with' => array(
'table2' => array(
'joinType' => 'LEFT JOIN',
'on' => 'pk = fk AND fk=1'
)
),
'select' => array('name',
'COALESCE(table2.price, t.standardprice) AS price',
'COALESCE(table2.period, t.period) AS period')
)
);
My goal is to pick table2's fields if those are filled in, but if these are empty / no rows found the original table's fields should be displayed.
However, my output isn't as expected. The price
field isn't displayed at all in my result's attributes, and the period
field is either table2's value or empty.
EDIT: Perhaps my SQL is wrong somewhere. Using this SQL gives me the wanted results:
SELECT name, COALESCE(tb1.price, tb2.standardprice) as price, COALESCE(tb1.period, tb2.period) as period
FROM table1 as tb1
LEFT JOIN table2 as tb2
ON (tb1.pk= tb2.fk) AND fk=1;
Yet I don't see any difference with my current code.
EDIT2: Table structures:
Table1 (original table)
pk (int 11) - Primary key, auto increment
name (varchar 255)
standardprice (decimal 11,2)
period (varchar 255)
fkLanguage //not relevant
photo //not relevant
description //not relevant
link //not relevant
Table2
ID (int 11) - Primary key, auto increment
fk (int 11) - Foreign key, which links to pk of table1
period (varchar 255)
price (decimal 11,2)
fkType //not relevant
amount //not relevant
Clarification: The fk=1
is indeed a JOIN condition. If the fk isn't 1 then I don't want those rows to join, but take the values from table1 instead.