SQL functions in SELECT

I created the following relations …

in my User class


'positions' => array(self::HAS_MANY,'UserPosition', 'uid', 'select'=>'DATEDIFF(NOW(), start_date) AS days', 

'order'=>'start_date DESC, end_date DESC, title', 

'with'=>'company'),

and in my UserPosition class I have


'company' => array(self::BELONGS_TO, 'Company', 'cid')

now when I dop something like


$jobs = $user->positions;

I get the following error

Active record "UserPosition" is trying to select an invalid column "DATEDIFF(NOW()". Note, the column must exist in the table or be an expression with alias.

I ended up creating the following inside UserPosition




public function findAllByUid($uid)

{

  $crit = new CDbCriteria(array(

    'select' => '*, PERIOD_DIFF(DATE_FORMAT(NOW(),\'%Y%m\'), DATE_FORMAT(start_date,\'%Y%m\')) as months',

    'condition' => 'uid=:uid',

    'order' => 'start_date DESC, end_date DESC, title',

    'params' => array(':uid'=>$uid),

  ));

  return $this->findAll($crit);

}



… and then calling $jobs = UserPosition::model()->findAllByUid($user->id); to get the data I wanted but want to know if there is a way to do this via relations. Btw, when I do somethiong like $this->with(‘company’)->findAll($crit); I again get that same error. Dunno if I am doing something horribly wrong here, first time ever that I am using a PHP framework.

I think this is a bug.

To the best of my knowledge, proper format would be




'positions' => array(

  self::HAS_MANY,'UserPosition', 'uid', 

  'select'=>'DATEDIFF(NOW(), ??.start_date) AS days'), 



No matter if I replace NOW() with a second column name, the "DATEDIFF(" part is absent from the generated SQL.

/Tommy

Ticket submitted: http://code.google.com/p/yii/issues/detail?id=648

Thanks, I’ll just use the other method for now.

i need the same trick but for me it is also not working. can you give me some advice?




'Tags' => array(self::MANY_MANY, 'Tag', 'DaytripTag(DaytripId, TagId)',

    'select' => 'GROUP_CONCAT(Tag)',

    'together' => true,

    'joinType' => 'INNER JOIN'),



error: Active record "Tag" is trying to select an invalid column "GROUP_CONCAT(Tag)". Note, the column must exist in the table or be an expression with alias.

Use an array to specify the ‘select’ option in this case.

Yep, works for me. Learning every day… ;)

To sum up the example given:




'positions' => array(

  self::HAS_MANY,'UserPosition', 'uid', 

  'select'=>array('DATEDIFF(NOW(), ??.start_date) AS days')),



Define ‘days’ in the UserPosition model.

Access with nested foreach.

/Tommy