[FIXED] How do you deal with aliases when using Yii's ORM?

Issue

$sql = "SELECT  `prod_id` AS  `id` , LOWER(  `prod_iso_code_5` ) AS  `prod_code` FROM `prod_data`";

I have this code and while the documentation tells you what to do in most cases they don't tell you what to do when you have a SQL function with a string as a parameter.

I did the following thinking this will work, but because the documentation doesn't cover all cases I am not 100% sure. Will the following command work?

$result =  $this->mydb->createcommand()
            ->select(array('prod_id AS id', 'LOWER("prod_iso_code_5") as prod_code')
            ->from('prod_data')
            ->queryAll();

Solution

It will not output what you are expecting. Because in your query builder you've used "prod_iso_code_5" as a string. Which is evaluated as string for LOWER() function. If you want to lower case the prod_data.product_iso_code_5 column's value as given in the query, you've to use either just product_iso_code_5 or wrap column name within ` (backtick) characters

$result =  $this->mydb->createcommand()
            ->select(array('prod_id AS id', 'LOWER(`prod_iso_code_5`) as prod_code')
            ->from('prod_data')
            ->queryAll();

Answered By - Gihan