Yii Active Record COALESCE不显示结果

问题描述：

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.

我有以下查询： p>

  $ result = Table1  :: model（） - ＆gt; findAll（array（
'with'=＆gt; array（
'table2'=＆gt; array（
'joinType'=＆gt;'LEFT JOIN'，
'on'  =＆gt;'pk = fk和fk = 1'
）
），
'选择'=＆gt;数组（'名称'，
'COALESCE（table2.price，t.standardprice）AS价格'，  
'COALESCE（table2.period，t.period）AS period'）
）
）; 
  code>  pre> 
 
 我的目标是选择table2的字段 填写，但如果这些是空的/没有找到行，则应显示原始表的字段。 p> 
 
 
但是，我的输出不是预期的。 结果的属性中根本不显示 price  code>字段， period  code>字段是table2的值或为空。 p> 
 
 
 编辑：也许我的SQL在某处错了。 使用这个SQL给我想要的结果： p> 
 
 

  SELECT name，COALESCE（tb1.price，tb2.standardprice）作为价格，COALESCE（tb1.period，tb2.period）as  period 
FROM table1 as tb1 
LEFT JOIN table2 as tb2 
ON（tb1.pk = tb2.fk）AND fk = 1; 
  code>  pre> 
 
 但是我不知道 看看我目前的代码有什么不同。 p> 
 
 
 EDIT2：表格结构： p> 
 
 
 表1  strong>（原始表格） p  > 
 
 
  pk（int 11） - 主键，自动增量
name（varchar 255）
standardprice（十进制11,2）
period（varchar 255）
fkLanguage //不相关
photo  //不相关
描述//不相关
link //不相关
  code>  pre> 
 
  表2  strong>  p> 
 
   ID（int 11） - 主键，自动增量
 nkk（int 11） - 外键，链接到table1 
period（varchar 255）的pk 
price（十进制11,2）
fkType // 不相关
amount //不相关
  code>  pre> 
 
 
 澄清 strong>：   fk = 1  code>   em>确实是一个JOIN条件。 如果fk不是1，那么我不希望这些行加入，而是从table1中获取值。 p> 
  div>

答

You need to add column price for parsing not existing column in schema.
Try to modify model Table1 ()

add public $price;
override method attributeNames to following:

public function attributeNames()
  {
      $colums = parent::attributeNames();
      $colums[] = 'price';
      return $colums;
  }

答

I think you should do like this:

$result = Tablename::model()->findAll(array(
'with' => array(
            'tabel2' => array(
                'joinType' => 'LEFT JOIN',
                'on' => 'pk = fk'
            )
        ),
'select' => array('name',
                  'COALESCE(tabel2.price, t.standardprice) AS price',
                  'COALESCE(tabel2.period, t.period) AS period'),
'condition'=> ' fk = 1 '
));

because fk = 1 is not a part of the on statement; it is only a condition. I think that will make the difference for you.

Yii Active Record COALESCE不显示结果

相关推荐