I've defined these relationships in my models:
Lead hasMany Job
Job HABTM Employee
Job HABTM Truck
I'm trying to do a find('all')
from my Truck model, and limit the results to:
- All Trucks,
- all jobs associated with those trucks that have a certain pickup date,
- the employees assigned to those jobs,
- and the lead associated with the job.
Here is my find operation:
// app/models/truck.php
$this->find('all', array(
'contain' => array(
'Job' => array(
'Employee',
'Lead',
'conditions' => array(
'Job.pickup_date' => $date
)
)
)
));
For some reason, Cake does the query to find Employees TWICE. This leads to having all employees represented two times for each job. Here is the SQL dump:
SELECT `Truck`.`id`, `Truck`.`truck_number` FROM `trucks` AS `Truck` WHERE 1 = 1;
SELECT `Job`.`id`, `Job`.`lead_id`, `Job`.`city`, `JobsTruck`.`id`, `JobsTruck`.`job_id`, `JobsTruck`.`truck_id` FROM `jobs` AS `Job` JOIN `jobs_trucks` AS `JobsTruck` ON (`JobsTruck`.`truck_id` IN (2, 3) AND `JobsTruck`.`job_id` = `Job`.`id`) WHERE `Job`.`pickup_date` = '2010-10-06'
SELECT `Lead`.`id`, `Lead`.`name`, `Lead`.`created` FROM `leads` AS `Lead` WHERE `Lead`.`id` = 4
SELECT `Employee`.`id`, `Employee`.`name`, `Employee`.`created`, `EmployeesJob`.`id`, `EmployeesJob`.`employee_id`, `EmployeesJob`.`job_id` FROM `employees` AS `Employee` JOIN `employees_jobs` AS `EmployeesJob` ON ( `EmployeesJob`.`job_id` = 1 AND `EmployeesJob`.`employee_id` = `Employee`.`id` )
SELECT `Lead`.`id`, `Lead`.`name`, `Lead`.`created` FROM `leads` AS `Lead` WHERE `Lead`.`id` = 4
SELECT `Employee`.`id`, `Employee`.`name`, `Employee`.`created`, `EmployeesJob`.`id`, `EmployeesJob`.`employee_id`, `EmployeesJob`.`job_id` FROM `employees` AS `Employee` JOIN `employees_jobs` AS `EmployeesJob` ON ( `EmployeesJob`.`job_id` = 1 AND `EmployeesJob`.`employee_id` = `Employee`.`id` )
Notice that the last two queries are duplicates. Did I do something wrong that I'm missing?