views:

15

answers:

0

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:

  1. All Trucks,
  2. all jobs associated with those trucks that have a certain pickup date,
  3. the employees assigned to those jobs,
  4. 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:

  1. SELECT `Truck`.`id`, `Truck`.`truck_number`
    FROM   `trucks` AS `Truck`
    WHERE  1 = 1;
  2. 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'
  3. SELECT `Lead`.`id`, `Lead`.`name`, `Lead`.`created` FROM `leads` AS `Lead`
    WHERE  `Lead`.`id` = 4
  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`
           )
  5. SELECT `Lead`.`id`, `Lead`.`name`, `Lead`.`created` FROM `leads` AS `Lead`
    WHERE  `Lead`.`id` = 4
  6. 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?