CakePHP: How can I change this find call to include all records that do not exist in the associated table?

Question

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'
)
)));

EDIT: As it turns out, my query works, there was a problem with my data that was causing all assignments to be returned.

Answer 1

Rember this condition will traslate to ON in the JOIN query, where as you need an WHERE somefield != somevalue kind of condition, use an conditions array ( see conditions in find ) in find method

$this->Modelname->find('type',array=('conditions' => array('somefiled !=' => $value,'somemorefield <>' => $value)));



Answer 2

There is nothing wrong with my query. Turns out there was a data issue preventing me from properly filtering the assignments.