title: CakePHP nesting two select queries
body: <p>I have two tables in cakePHP.</p>
  
  <pre><code>competencies
  ------------
  id
  name
  
  competenceRatings
  -----------------
  id
  competence_id
  user_id
  rating
  </code></pre>
  
  <p>I need a way to write the following query in the cake way: </p>
  
  <p>SELECT * FROM competencies WHERE id NOT IN (SELECT competence_id FROM competence_ratings WHERE employee_id = $userId)</p>
  
  <p>Someone please help me!! </p>
  
  <p>What i did before going to this subquery method:</p>
  
  <p>I tried competencies->hasMany->competenceRatings, competenceRatings->belongsTo->competencies relations.</p>
  
  <pre><code>$competencies = $this-&gt;Competence-&gt;CompetenceRating-&gt;find('all',array('CompetenceRating.user_id' =&gt; $userId,'CompetenceRating.competence_id !=' =&gt; 'Competence.id'));
  </code></pre>
  
  <p>I want to be able to get the names of competencies for which a user have NOT made any ratings into competenceRatings table. i.e., I need list of names from competencies table for which there are no entries in comptenceRatings table(for given user_id).</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I tried table join also:</p>
  
  <pre><code>$options['joins'] = array(
              array(
                  'table' =&gt; 'competence_ratings',
                  'alias' =&gt; 'CompetenceRating',
                  'type' =&gt; 'LEFT OUTER',
                  'conditions' =&gt; array(
                      'Competence.id = CompetenceRating.competence_id'
                  )
              )
          );
  $options['conditions'] = array( 'CompetenceRating.employee_id' =&gt; $employee['Employee']['id'] );
  
  $competencies = $this-&gt;Competence-&gt;find('all',$options);
  </code></pre>