Yii Framework Forum: SQL functions in SELECT - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

SQL functions in SELECT Rate Topic: -----

#1 User is offline   xyber 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 24-October 09

Posted 24 October 2009 - 10:26 AM

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.
0

#2 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 24 October 2009 - 12:03 PM

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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#3 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 24 October 2009 - 01:34 PM

Ticket submitted: http://code.google.c...s/detail?id=648
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#4 User is offline   xyber 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 24-October 09

Posted 25 October 2009 - 04:10 AM

View Posttri, on 24 October 2009 - 01:34 PM, said:



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

#5 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 27 October 2009 - 06:08 AM

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.
0

#6 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,876
  • Joined: 04-October 08
  • Location:DC, USA

Posted 27 October 2009 - 07:47 AM

Use an array to specify the 'select' option in this case.
0

#7 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 27 October 2009 - 07:29 PM

View Postqiang, on 27 October 2009 - 07:47 AM, said:

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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users