I have table user and table look-in, where are the users shifts. Im trying to get all staff members
from users table with their relational shifts. The problem is that the following piece of code is returning
JUST the users that has at least one shift (look-ins) that match the condition but I would like to get also those user instances that do not match the time conditions settled for the shifts.
Those users intances (having no shifts) should be just simple user AR objects with $userShifts->lookIns returning simply an empty array. I’m trying to do left join but it does not work.
$cri = new CDbCriteria();
$cri->addCondition("type LIKE 'staff'");
$userShifts = User::model()->with(array("lookIns"=>array("joinType"=>"LEFT JOIN", "condition"=> "date LIKE '$time'")))->findAll($cri);
PROBLEM IS: to get AR-Records which has no relational data and those which has some relational data. Current returns just the latter.
In SQL while evaluating an expression when one side of a comparison operator is NULL the whole expression evaluates to NULL instead of boolean true/false, and NULL is treated as false. That’s why your LIKE conditions turn out false when there is no related row. You can do two things:
Check for null values: lookIns.date IS NOT NULL AND lookIns.date LIKE ‘$time’
Put the condition in the ‘on’ property instead of 'condition:
with(array("lookIns"=>array("joinType"=>"LEFT JOIN", "on"=> "date LIKE '$time'")))
Also, if $time is a user entered value NEVER pass it directly to a SQL statement, always use placeholders/param binding:
with(array("lookIns"=>array("joinType"=>"LEFT JOIN", "on"=> "date LIKE :time", 'params'=>array(':time'=>$time))))
Actually, I relized that there is still one problem with the query. For some reason it returns all the intances of lookIns and seems to ignore day condition. I mean $user->lookIns is returning a big array instead of array with one look-in that it is supposed to do according to day condition.
Great, that worked and makes sense! Thanks a lot!
Even thought the $time doesn’t come from user now I agree that it is still good idea to put it on the params just in case for latter modifications - thanks for advice.
