SQL functions in SELECT


(Pl Young) #1

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.


(Ft07) #2

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


(Ft07) #3

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


(Pl Young) #4

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


(Bas Vdl) #5

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.


(Qiang Xue) #6

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


(Ft07) #7

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