title: Use nested SELECT instead of additional joins
body: <p>When using a INNER JOIN in a default scope, the INNER JOIN is applied to the wrong table.
  I have 4 models: <code>Task</code>, <code>Project</code>, <code>ProjectUser</code> and <code>User</code>. Their structure, relations and default scope can bee seen below.</p>
  
  <p>A project is only allowed to be visible to a User, when that user is assigned to the project (through a ProjectUser link). Each project has many tasks. A task has a BELONGS_TO relation to it's parent Project. A task is only allowed to be seen by the User, if the parent Project is also visible to that User. </p>
  
  <p>When I query:</p>
  
  <pre><code>$tasks = Task::model()-&gt;withoutChildren()-&gt;findAll();
  </code></pre>
  
  <p>it returns nothing.</p>
  
  <p>My models: (heavily simplified for this post)</p>
  
  <pre><code>class Task extends CActiveRecord {
      /*
      Columns:
      id
      parent_task_id
      project_id
      */
  
      public function relations() {
          return array(
              'project' =&gt; array(self::BELONGS_TO, 'Project', 'project_id'),
              'subTasks' =&gt; array(self::HAS_MANY, 'Task', 'parent_task_id', 'alias' =&gt; "Task_subTasks"),
              'parentTask' =&gt; array(self::BELONGS_TO, 'Task', 'parent_task_id'),
          );
      }
  
  
      public function scopes() {
          $t = $this-&gt;tableAlias;
          return array(
              'withoutChildren'=&gt;array(
                  'with' =&gt; array("subTasks"),
                  'select' =&gt; "COUNT(Task_subTasks.id) AS subTaskCount",
                  'group' =&gt; "$t.id",
                  'having' =&gt; "subTaskCount = 0",
              )
          );
      }
  
      public function defaultScope() {
          $t = $this-&gt;getTableAlias(false, false);
          return array(
              'with' =&gt; array(
                  "project" =&gt; array(
                      'select' =&gt; false,
                      'alias' =&gt; "Task_def_". $t,
                      'joinType' =&gt; "INNER JOIN"
                  )
              )
          ); 
      }
  }
  
  class Project extends CActiveRecord {
      /*
      Columns:
      id
      */
  
      public function relations()
      {
          return array(
              'projectUsers' =&gt; array(self::HAS_MANY, 'ProjectUser', 'project_id'),
              'tasks' =&gt; array(self::HAS_MANY, 'Task', 'project_id'),
              'users' =&gt; array(self::MANY_MANY, 'User', 'project_user(project_id, user_id)'),
          );
      }
  
      public function defaultScope() {
          $t = $this-&gt;getTableAlias(false, false);
          return array(
              'with' =&gt; array(
                  'projectUsers' =&gt; array(
                      'joinType' =&gt; "INNER JOIN",
                      'alias' =&gt; "{$t}_pu",
                      'on' =&gt; "{$t}_pu.user_id=". User::current()-&gt;id
                  )
              )
          );
      }
  
  }
  
  class ProjectUser extends CActiveRecord {
      /*
      Columns:
      id
      project_id
      user_id
      */
  
      public function relations()
      {
          return array(
              'project' =&gt; array(self::BELONGS_TO, 'Project', 'project_id'),
              'user' =&gt; array(self::BELONGS_TO, 'User', 'user_id'),
          );
      }
  }
  
  class User extends CActiveRecord {
  
  }
  </code></pre>
  
  <p>I do understand why it doesn't work. The query yii generates, is: (again, heavily simplified)</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT COUNT(Task_subTasks.id) AS subTaskCount, (all the other field...) FROM `task` `t`  
  
  INNER JOIN `project` `Task_def_t` ON (`t`.`project_id`=`Task_def_t`.`id`) AND 
  
  INNER JOIN `project_user` `Task_def_t_pu`
  ON (`Task_def_t_pu`.`project_id`=`Task_def_t`.`id`) AND
  (Task_def_t_pu.user_id=6)  
  
  LEFT OUTER JOIN
  `task` `Task_subTasks` ON (`Task_subTasks`.`parent_task_id`=`t`.`id`) AND
  
  INNER JOIN `project`
  `Task_def_Task_subTasks` ON
  (`Task_subTasks`.`project_id`=`Task_def_Task_subTasks`.`id`) AND
  
  INNER JOIN `project_user`
  `Task_def_Task_subTasks_pu` ON
  (`Task_def_Task_subTasks_pu`.`project_id`=`Task_def_Task_subTasks`.`id`)
  AND (Task_def_Task_subTasks_pu.user_id=6)  
  
  GROUP BY t.id 
  
  HAVING (subTaskCount = 0) 
  </code></pre>
  
  <p>In this case the 4th and 5th JOINs apply to the <code>'task' 't'</code> table. Which shouldn't happen. Now they limit the original query even more, which results in the query returning 0 rows. </p>
  
  <p>However the 4th and 5th JOINs should be applied to <code>LEFT OUTER JOIN 'task' 'Task_subTasks'</code> (I'm not sure 'applied' is the correct term here). Anyway, this is what I expect Yii to generate: </p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT (all the other field...) FROM `task` `t`  
  
  INNER JOIN `project` `Task_def_t` ON (`t`.`project_id`=`Task_def_t`.`id`) AND 
  
  INNER JOIN `project_user` `Task_def_t_pu`
  ON (`Task_def_t_pu`.`project_id`=`Task_def_t`.`id`) AND
  (Task_def_t_pu.user_id=6)  
  
  WHERE (
      SELECT COUNT(Task_subTasks.id) FROM `task` `Task_subTasks`
  
      INNER JOIN `project`
      `Task_def_Task_subTasks` ON
      (`Task_subTasks`.`project_id`=`Task_def_Task_subTasks`.`id`) AND
  
      INNER JOIN `project_user`
      `Task_def_Task_subTasks_pu` ON
      (`Task_def_Task_subTasks_pu`.`project_id`=`Task_def_Task_subTasks`.`id`)
      AND (Task_def_Task_subTasks_pu.user_id=6)  
  
      WHERE (`Task_subTasks`.`parent_task_id`=`t`.`id`)
  
  ) = 0
  
  GROUP BY t.id 
  </code></pre>
  
  <p>I edited this simple query without testing. However, I transformed the original (massive) query the same way, and now it works!</p>
  
  <p>My question: how do I instruct Yii to query it using a nested SELECT instead of appending all the joins to the same table?</p>