I have a few tables with the following relationships:
Company hasMany Jobs, Employees, and Trucks, Users
I've got all my foreign keys set up properly, along with the tables' Models, Controllers, and Views.
Originally, the Jobs table had a boolean field called "assigned". The following find operation (from the JobsController) successfully returns all employees, all trucks, and any jobs that are not assigned and fall on a certain day for a single company (without returning users by utilizing the containable behavior):
$this->set('resources', $this->Job->Company->find('first', array(
'conditions' => array(
'Company.id' => $company_id
),
'contain' => array(
'Employee',
'Truck',
'Job' => array(
'conditions' => array(
'Job.assigned' => false,
'Job.pickup_date' => date('Y-m-d', strtotime('Today'));
)
)
)
)));
Now, since writing this code, I decided to do a lot more with the job assignments. So I've created a new model "Assignment" that belongsTo Truck and belongsTo Job. I've added the hasMany Assignments to both the Truck model and the Jobs Model. I have both foreign keys in the assignments table, along with some other assignment fields.
Now, I'm trying to get the same information above, only instead of checking the assigned field from the job table, I want to check the assignments table to ensure that the job does not exist there. I can no longer use the containable behavior if I'm going to use the "joins" feature of the find method due to mysql errors (according to the cookbook). But, the following query returns all jobs, even if they fall on different days.
$this->set('resources', $this->Job->Company->find('first', array(
'joins' => array(
array(
'table' => 'employees',
'alias' => 'Employee',
'type' => 'LEFT',
'conditions' => array(
'Company.id = Employee.company_id'
)
),
array(
'table' => 'trucks',
'alias' => 'Truck',
'type' => 'LEFT',
'conditions' => array(
'Company.id = Truck.company_id'
)
),
array(
'table' => 'jobs',
'alias' => 'Job',
'type' => 'LEFT',
'conditions' => array(
'Company.id = Job.company_id'
)
),
array(
'table' => 'assignments',
'alias' => 'Assignment',
'type' => 'LEFT',
'conditions' => array(
'Job.id = Assignment.job_id'
)
)
),
'conditions' => array(
'Job.pickup_date' => $day,
'Company.id' => $company_id,
'Assignment.job_id IS NULL'
)
)));
