Problems with activerecord calculated fields and relations

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?

Have you tried using the overriding the defaultScope() method in your Articles class? This could be a workaround for your problem.

Thanks, Bravoman, for your suggestion, and it might well work if I always want the same field. However, if I need different calculated fields for different relationships, I would need to add them all in each time, which I don’t feel is correct.

Moreover, if the calculated field uses one of the fields from a linked table, there is no guarantee that that table will always be available, and would clearly cause other errors.

Also, as I showed in the last example, this error is preventing the use of standard MySQL functions in the query. I suspect the query is being parsed and split into ‘fields’ whereever a space or punctuation character appears, rather than fully analysing the text. So, what is really needed is a simple way to add a calculated field in at any point, without the parser mistakenly assuming you’ve added an invalid field

Have you looked at the query that is created by Yii? If not use the following in your config to see them in your browser:

return array(

	...

	'components'=>array(

		...

		'log'=>array(

			'class'=>'CLogRouter',

			'routes'=>array(

				array(

					'class'=>'CWebLogRoute',

					'levels'=>'trace, info',

				),

			),

		),

	),

)

Thanks again for the suggestion, and I do already have logging enabled.

I’ve moved the project on a lot since I raised this question and can’t check it with any ease at the moment. As far as I remember though, no query is actually generated as the framework detects the ‘invalid column’ and raises an exception before it gets to that point.

Ok, clear enough Have you done a bug report about this? This seems like a bug to me.

No, good point. I’ll try to get that done today.

SQL functions with more than one parameter need to be enclosed in an array.

/Tommy

I raised a bug report and had a reply from Qiang within 5 minutes - pretty impressive turnaround time.

The solution is to use an array for the select instead of a string, so changing the above to

public function relations()

{

return array(


    'articles' => array(self::HAS_MANY, 'Articles', 'school_id',


                         'select'    => array(' *', 'date_format(date, "%d %M %Y") AS date'),


                         'order' => 'updated_at DESC'),


    );

}

causes the processing to succeed. Simple, if not intuitive.

Good to know! Yet again there is more amazement around every corner in working with this framework