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?
