CakePHP model->find and IF condition in field

Question

I need to construct a cakePHP model query like this:

SELECT `Translate`.`var_name` , IF( prove_cn_sim =1, lang_cn_sim, lang_en )
FROM 'translate` AS `Translate`

I've tried:

$this->find("list", array(
            'fields' => array("var_name","IF(prove_cn_sim = 1, lang_cn_sim, lang_en)"),
        ));

but I got something like this:

SELECT `Translate`.`var_name`, Translate.IF(prove_cn_sim = 1, lang_cn_sim, lang_en) FROM `translate` AS `Translate`

CakePHP add table prefix to IF. do you know any solutions?

Answer 1

Try adding a virtual field to your Translate model:

public $virtualFields = array(
    'lang' => 'IF(Translate.prove_cn_sim = 1, Translate.lang_cn_sim, Translate.lang_en)'
);

Then you can always read the correct value from the virtual lang field when using finds on the model. Which would in your case become something like:

$this->find('list', array(
    'fields' => array('var_name', 'lang'),
));