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