Yii Framework Forum: Querybuilder Andwhere() Where() Order. - Yii Framework Forum

Jump to content

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

Querybuilder Andwhere() Where() Order. Rate Topic: -----

#1 User is offline   Jonny 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 181
  • Joined: 26-December 11

Posted 26 September 2013 - 09:13 PM

I have this query

		$command = Yii::app()->db->createCommand()
		....
		->leftjoin('i as i', 'i.user_id = u.id')
		->andWhere('i.date_added > DATE_SUB(u.date_expired, INTERVAL  30 DAY)')
		->where(array('in', 'u.id', $licenses))			
		->group('u.id')
		->order('u.first_name');


I did this as and I get:


LEFT JOIN `item` `i` ON i.user_id = u.id
WHERE `u`.`id` IN ('15', '2', '13')
GROUP BY `u`.`id`
ORDER BY `u`.`first_name`


The andWhere clause doesn't seem to being implemented now.

What I actually wanted was this SQL output


  FROM user u LEFT JOIN item i 
    ON i.user_id = u.id
   AND i.date_added > u.date_expired - INTERVAL  30 DAY
 WHERE u.id IN (1, 2, 3) 
 GROUP BY u.id, u.date_expired, u.first_name, u.last_name


Is it possible to do this with Query builder? Not sure how to alter the order between statements. I'm doing this as currently My method won't allow me to return a row with a zero COUNT() in the select part.

I think i basically need

->andWhere('i.date_added > DATE_SUB(u.date_expired, INTERVAL  30 DAY)')


To be just 'AND' in the SQL output. But unsure how to do this
Jonny
0

#2 User is offline   Ankit Modi 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 890
  • Joined: 19-February 13
  • Location:India

Posted 27 September 2013 - 02:10 AM

Hi I have put the all condition i hope it;s some help.

$criteria=new CDbCriteria;
$criteria->select = "priority";
$criteria->order = "priority desc";
return new CActiveDataProvider($model, array(   'criteria'=>$criteria,  ));

-----
$criteria=new CDbCriteria;
$criteria->compare('year(`EventStartdate`)','>=:'.$currentYear); 
$criteria->compare('year(`EventStartdate`)','>='.$currentYear); 

-----
$criteria = new CDbCriteria;
$criteria->condition = 'foo = 1 OR bar = 2';
$models = TableName::model()->findAll($criteria);

-----
$criteria=new CDbCriteria;
       /** together **/
$criteria->together=true;      
       /** with **/  
$criteria->with=array('tbl_user');
       /** inner join **/
$criteria->join="INNER JOIN tbl_user as user ON(user.userid=t.user_id)";                
        
       /** compare **/
$criteria->compare('t.isactive',$this->isactive,true);
        
       /** addCondition **/
$criteria->addCondition('isactive=1','AND');
        
       /** addInCondition : pass array**/
$criteria->addInCondition('categoryid',$this->categoryid,true);                  
        
        /** addBetweenCondition **/
$criteria->addBetweenCondition("t.createdon",$this->date_after,$this->date_before,"AND");
        
$criteria->addCondition("$condition");                               
---

Thanks,
Ankit Modi
Skype : amodi06
1

#3 User is offline   Jonny 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 181
  • Joined: 26-December 11

Posted 27 September 2013 - 02:38 PM

Thanks for the reply, it is a good answer. I have now used CDbCriteria() to add the condition but i am struggling to add it to the join. Currently it is now coming after the WHERE
0

#4 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,082
  • Joined: 16-February 11
  • Location:Japan

Posted 27 September 2013 - 09:03 PM

Hi Jonny,

The reference of leftJoin seems to tell that you have to write like this:
$command = Yii::app()->db->createCommand()
....
->leftjoin('i as i', 'i.user_id = u.id AND i.date_added > DATE_SUB(u.date_expired, INTERVAL 30 DAY)')
->where(array('in', 'u.id', $licenses))			
->group('u.id')
->order('u.first_name');

1

#5 User is offline   Jonny 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 181
  • Joined: 26-December 11

Posted 27 September 2013 - 10:43 PM

You are correct! Thank you. I did try this, but i neglected to comment out my addWhere() clause.

Thank you :)
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