CODEWITHSUNDEEP
cakephpjoin
Joe
Joe

Reputation: 8609

Why is my cake join throwing in a LEFT JOIN?

I’m working on a join for two HABTM relationships.

I have a table named projects and it has a HABTM relationship with the users table. It uses the intermediary table projects_users, for its HABTM relationship.

Then the users table has a HABTM relationship with departments (using the department_users table). Here is my join statement (which is in my projects_controller):

$projectsUsersDepartments['joins'] = array(
    array('table' => 'users',
        'alias' => 'User',
        'type' => 'inner',
        'conditions' => array(
            'User.id = ProjectsUser.user_id'
        )
    ),
    array('table' => 'departments_users',
        'alias' => 'DepartmentsUser',
        'type' => 'inner',
        'conditions' => array(
            'DepartmentsUser.user_id = User.id'
        )
    )
);

$projectsUsersDepartments['conditions'] = array(
    'ProjectsUser.project_id' => 1,
    'DepartmentsUser.department_id' => 25
);

$projectsUsers = $this->Project->ProjectsUser->find('all', $projectsUsersDepartments);

Here is the SQL it’s producing:

SELECT `ProjectsUser`.`id`, `ProjectsUser`.`user_id`, `ProjectsUser`.`project_id`, `ProjectsUser`.`status`, `User`.`id`, `User`.`contractor_id`, `User`.`group_id`, `User`.`title`, `User`.`last_name`, `User`.`first_name`, `User`.`email`, `User`.`main_phone`, `User`.`cell_phone`, `User`.`fax`, `User`.`address`, `User`.`city`, `User`.`state`, `User`.`zipcode`, `User`.`username`, `User`.`password`, `User`.`is_active`, `User`.`is_verified`, `User`.`is_id_verified`, `User`.`last_login`, `User`.`last_password_change`, `User`.`created`, `User`.`modified` 
FROM `projects_users` AS `ProjectsUser` 
inner JOIN `users` AS `User` ON (`User`.`id` = `ProjectsUser`.`user_id`) 
inner JOIN `departments_users` AS `DepartmentsUser` ON (`DepartmentsUser`.`user_id` = `User`.`id`) 
LEFT JOIN `users` AS `User` ON (`ProjectsUser`.`user_id` = `User`.`id`)  
WHERE `ProjectsUser`.`project_id` = 1 AND `DepartmentsUser`.`department_id` = 25

I cannot understand why the join is throwing in the line:

LEFT JOIN `users` AS `User` ON (`ProjectsUser`.`user_id` = `User`.`id`)

It’s causing a SQL Error: 1066: Not unique table/alias: 'User' error, because User is being defined twice.

I don’t see how I’m telling the join statement to do that. Is there something I’m missing in my understanding of cake joins? Thanks!

Upvotes: 0

Views: 99

Answers (1)

Dave
Dave

Reputation: 29141

It's considered good practice to set public $recursive = -1; in your AppModel - that way, your finds won't automatically add stuff without your knowledge.

"Recursive" is a nice idea, but should never really be used in practice, which is why it's been completely removed in Cake 3.

In this case, recursive being greater than -1 is likely your problem.

Upvotes: 1

Related Questions