$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