Use Nested Select Instead Of Joins.

When using a INNER JOIN in a default scope, the INNER JOIN is applied to the wrong table.

I have 4 models: Task, Project, ProjectUser and User. Their structure, relations and default scope can bee seen below.

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.

When I query:


    $tasks = Task::model()->withoutChildren()->findAll();

it returns nothing.

My models: (heavily simplified for this post)


    class Task extends CActiveRecord {

    	/*

    	Columns:

    	id

    	parent_task_id

    	project_id

    	*/

    

    	public function relations() {

    		return array(

    			'project' => array(self::BELONGS_TO, 'Project', 'project_id'),

    			'subTasks' => array(self::HAS_MANY, 'Task', 'parent_task_id', 'alias' => "Task_subTasks"),

    			'parentTask' => array(self::BELONGS_TO, 'Task', 'parent_task_id'),

    		);

    	}

    

    

    	public function scopes() {

    		$t = $this->tableAlias;

            return array(

                'withoutChildren'=>array(

                    'with' => array("subTasks"),

                    'select' => "COUNT(Task_subTasks.id) AS subTaskCount",

                    'group' => "$t.id",

                    'having' => "subTaskCount = 0",

                )

            );

        }

    

        public function defaultScope() {

    		$t = $this->getTableAlias(false, false);

        	return array(

        		'with' => array(

        			"project" => array(

        				'select' => false,

        				'alias' => "Task_def_". $t,

        				'joinType' => "INNER JOIN"

        			)

        		)

            ); 

        }

    }

    

    class Project extends CActiveRecord {

    	/*

    	Columns:

    	id

    	*/

    

    	public function relations()

    	{

    		return array(

    			'projectUsers' => array(self::HAS_MANY, 'ProjectUser', 'project_id'),

    			'tasks' => array(self::HAS_MANY, 'Task', 'project_id'),

    			'users' => array(self::MANY_MANY, 'User', 'project_user(project_id, user_id)'),

    		);

    	}

    

    	public function defaultScope() {

    		$t = $this->getTableAlias(false, false);

            return array(

                'with' => array(

                	'projectUsers' => array(

    	            	'joinType' => "INNER JOIN",

    	            	'alias' => "{$t}_pu",

    	            	'on' => "{$t}_pu.user_id=". User::current()->id

    	            )

                )

            );

        }

    

    }

    

    class ProjectUser extends CActiveRecord {

    	/*

    	Columns:

    	id

    	project_id

    	user_id

    	*/

    

    	public function relations()

    	{

    		return array(

    			'project' => array(self::BELONGS_TO, 'Project', 'project_id'),

    			'user' => array(self::BELONGS_TO, 'User', 'user_id'),

    		);

    	}

    }

    

    class User extends CActiveRecord {

    	

    }

I do understand why it doesn’t work. The query yii generates, is: (again, heavily simplified)


    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) 

In this case the 4th and 5th JOINs apply to the 'task' 't' table. Which shouldn’t happen. Now they limit the original query even more, which results in the query returning 0 rows.

However the 4th and 5th JOINs should be applied to LEFT OUTER JOIN 'task' 'Task_subTasks' (I’m not sure ‘applied’ is the correct term here). Anyway, this is what I expect Yii to generate:


    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 

I edited this simple query without testing. However, I transformed the original (massive) query the same way, and now it works!

My question: how do I instruct Yii to query it using a nested SELECT instead of appending all the joins to the same table?

Bump :ph34r:

Anyone? :mellow: