Querybuilder Andwhere() Where() Order.

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

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

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');



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

Thank you :)