Hi all,
I’m having problems with calculated field in database queries with relationships and hope someone can explain how to add them
Adding the fields appears to work fine when the table is referenced directly, but not if used in a relationship. For example, I use the mysql date_format function to produce a formatted date, rather than translate it in php. When I use a call such as
$articles = Articles::model()->findAll(array (
'select' => ' date_format(date, "%d %M %Y") AS date',
'order' => 'updated_at DESC')
) ;
it works fine, and I obtain the date column in the expected format.
However, when I add the criteria to a relationship in a second model, for example
public function relations()
{
return array(
'articles' => array(self::HAS_MANY, 'Articles', 'school_id',
'select' => ' *, date_format(date, "%d %M %Y") AS date',
'order' => 'updated_at DESC'),
);
}
and call it like
$author = Authors::model()->findBypk(1) ;
$articles = $author->articles (array('condition' => 'article_type_id=3')) ;
I always get the error
Active record "Articles" is trying to select an invalid column "date_format(date".
Note, the column must exist in the table or be an expression with alias.
I have tried assigning a different alias name, such as mydate and declaring it at the top of the model
class Articles extends CActiveRecord
{
public $mydate ;
but it makes no difference.
I’ve also tried with simple transformations, such as the following for three and date,. These also work perfectly when no relations are involved, but result in the same error message when included in the relation
public function relations()
{
return array(
'articles' => array(self::HAS_MANY, 'Articles', 'school_id',
'select' => ' 1/2 AS three, IFNULL(date, 2) AS date
)
);
}
Is there a know limitation using calculated fields with related tables, or am I doing something wrong?