Add case argument when Yii SQL statement

Question

In YII, I have an already working function:

$sql = Yii::app()->db->createCommand(); $sql->select('A.name as client_name, B.name as product_name'); $sql->join('tableb B', 'A.id=B.product_id'); $sql->from('tablea A'); $sql->where('1 = 1'); 

Now I'm trying to add tiny logic to the product_name field, in MYSQL it will be

 CASE WHEN B.name = "sth" THEN B.name ELSE B.another_name END AS product_name 

Is it possible to add this case when the block in the select () function?

Answer 1

I asked this question here after I struggled for 2 hours and searched a lot here. But after 10 minutes I found that I got an answer ...

By looking at the source code of the select () method,

 public function select($columns='*', $option='') { if(is_string($columns) && strpos($columns,'(')!==false) $this->_query['select']=$columns; else { if(!is_array($columns)) $columns=preg_split('/\s*,\s*/',trim($columns),-1,PREG_SPLIT_NO_EMPTY); foreach($columns as $i=>$column) { if(is_object($column)) $columns[$i]=(string)$column; else if(strpos($column,'(')===false) { if(preg_match('/^(.*?)(?i:\s+as\s+|\s+)(.*)$/',$column,$matches)) $columns[$i]=$this->_connection->quoteColumnName($matches[1]).' AS '.$this->_connection->quoteColumnName($matches[2]); else $columns[$i]=$this->_connection->quoteColumnName($column); } } $this->_query['select']=implode(', ',$columns); } if($option!='') $this->_query['select']=$option.' '.$this->_query['select']; return $this; } 

Pay attention to the 1st if statement, when the line is passed and contains "(", the $ columns variable will be returned directly, this is really what I'm looking for!

So the solution would be:

 $sql->select('A.name as client_name, (CASE WHEN B.name = "sth" THEN B.name ELSE B.product_name END ) as product_name'); 

Make sure that the "as product_name" alias does not contain a section ().