Modelclass: How To Select Column With Formatting

Yii 1.1 General Discussion
1

Hello All,

I’m trying to select some different column with formatting like:




Article::model()

->findAll(array(

'select' => "DATE_FORMAT(`t`.`article_date`, '%b %D %Y') AS `t`.`display_date`,  (`t`.`col1` + `t`.`col2`) AS `t`.`relevance`",

'order' => "`t`.`relevance` DESC"

));

When Executing above code I’m getting this error message:




Active record "Article" is trying to select an invalid column "'%b %D  %Y') AS `t`.`display_date`".

Note, the column must exist in the table or  be an expression with alias.

THANKS

2

I believe that the problem is with your aliasing. Try this instead:




Article::model()->findAll(array(

    'select' => "DATE_FORMAT(`t`.`article_date`, '%b %D %Y') AS display_date,  (`t`.`col1` + `t`.`col2`) AS relevance",

    'order' => "relevance DESC",

));
3

I don’t think so because error message showing for “invalid column "’%b %D %Y’) AS t.display_date”.

Even this is stopping on DATE_FORMAT(t.article_date, ‘%b %D %Y’) AS display_date.

4

It seems like it’s treating the comma in DATE_FORMAT as a column separator, which suggests that the query isn’t being built correctly. I suspect that Yii is doing some processing which is splitting the string on the comma.

See if defining the select clause as an array helps:




Article::model()->findAll(array(

    'select' => array(

        "DATE_FORMAT(`t`.`article_date`, '%b %D %Y') AS display_date",

        "(`t`.`col1` + `t`.`col2`) AS relevance",

    ),

    'order' => "relevance DESC",

));

The documentation says that the select attribute expects an array of column names, so this might not be the right way to handle this.

EDIT:

Delving into the source code here, Yii does indeed split the string on the comma character. From what I can see, passing it as an array should work. It seems to only do further processing if it can’t find a ‘(’ character.

5

Well Keith,

It’s working when I’m running like this:




Article::model()->findAll(array(

	'select' => "`t`.`article_date`,  (`t`.`col1` + `t`.`col2`) AS relevance",

	'order' => "relevance DESC",

));

Seems It’s does not allowing database stored function. When I used with function ‘FORMAT_DATE’ then the query was without function like ‘., t.article_date, ‘%b %D %Y’) AS display_date, .’

6

Try the answer from my follow up post.

7

Thanks Keith,

This work

Thanks again

8

I tried and stucked with one more error.

When I’m assessing display_date property then it throwing error:

[color="#FF0000"]Property "Article.display_date" is not defined. [/color]




$model = Article::model()->findAll(array(

'select' => array(

    	"DATE_FORMAT(`t`.`article_date`, '%b %D %Y') AS display_date",

    	"(`t`.`col1` + `t`.`col2`) AS relevance",

	),

	'order' => "relevance DESC",

));


foreach($model as $each) {

	echo $each->display_date;

}
9

You need an attribute within your model to hold that value.

At the top of your class:




class Article extends CActiveRecord

{

    public $display_date;

    ...

}

Alternatively, you can use property syntax.

10

Thanks a lot again