Why is CakePHP duplicating my queries? (this is not the same "too many queries" problem that is usually asked)

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:

开发者_StackOverflow社区

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?

UPDATE

It seems Cake sends a duplicate query for every truck. Now that I have 15 records in the trucks table, the queries to leads and employees are duplicated 15 times each.

---

I don't know why there are two queries duplicated but maybe this behavior can help:

https://github.com/Terr/linkable

UPDATE

This kind of problem is well known:

• Duplicate Queries in MODEL->HABTM->HABTM->HasMany relationship
• Duplicate Queries Problem with Containable
• Ticket

---

There is nothing to join the found Job/s to a specific Truck.

(I hope my explanation isn't too hard to understand, but CakePHP can be that way sometimes! imho)

The Jobs are being attributed to the Trucks in an almost arbitrary way (my memory of Cake is that this can happen); the nature of the HABTM call attaches the Job/s to each of the 15 Trucks. This seems to be the current process from my point of view;

• Get all trucks.
• Get all jobs for those trucks where the date is x.
• [Problem 1] Attach the found Jobs to each Truck (that is your 15 trucks), but attached to every Truck.
• [Problem 2] Get all Leads/Employees related to that Job, again for each Truck.

Problem 1: The source of the issue. You can see in the second query (SELECT Job...), where it uses the correct truck_id's in the ON statement, but Cake cannot "join" these Jobs back into the right Truck, because it is a different query! So it joins the found jobs to each Truck.

Problem 2: This is the 'real' problem, for Cake does not construct long JOIN statements, so there is no way to find the Employees/Leads only for those Trucks that you want. That is why it finds them for each Truck, this is because you are doing a FindAll on Truck.

I hope that makes sense. You need to do a find all on Jobs, since that is the 'center' of the query (pickup_date).

$this->loadModel('Job');
$whatev = $this->Job->find('all', array(
    'contain' => array(
        'Job' => array(
            'Truck',
            'Employee',
            'Lead',
            'conditions' => array(
                'Job.pickup_date' => $date
            )
        )
    )
));

CakePHP queries really only work when you find one/all of a certain model, it is better to start in the middle and work either side if you have a double HABTM relationship. If you wish to 'sort' by Truck, then you might have to write you're own query (model method) to accomplish the task yourself. In raw SQL this query may be easy, in abstracted PHP super-cake-ness this is difficult for CakePHP to allow.

Happy baking, as they say!

---

You want LEFT JOIN instead of JOIN.

Don't ask me how to do it in cakephp, i'm happy my code currently works. ^^