SQL error 1064 in a hasMany through relationship

Question

I've been trying to solve this for the past 3 days and nothing has came up. I've researched a lot and this errors usually come up when CakePHP can't find my model or I have some name wrong in my relationships. Well I tried looking at everything, but still couldn't find where the error is. I have a User model, a Project model and a UserRole model which is the join table used by the hasMany through relationship.

File names:

user.php project.php user_role.php

Models:

class UserRole extends AppModel {
...
var $belongsTo = array(
    'User' => array(
        'className' => 'User',
        'foreignKey' => 'user_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    ),
    'Project' => array(
        'className' => 'Project',
        'foreignKey' => 'project_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    )
);
class User extends AppModel {
...
var $hasMany = array(
...
    'UserRole' => array(
        'className' => 'UserRole',
        'foreignKey' => 'user_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    ),

);
class Project extends AppModel {
...
var $hasMany = array(
...
    'UserRole' => array(
        'className' => 'UserRole',
        'foreignKey' => 'project_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    ),
);

When I try calling any method from UserRole from User, it give me the 1064 SQL error. Any hint on where could be the problem?

Things I've tried so far: checked if UserRole is being loaded, and it is. And I can call the UserRole functions from an element, they are working fine.

FUNCTIONS (get_related_clients is in Users and the other is in UserRole):

function get_related_clients ($id_user, $relationship_type) {
$id_names = $this->User->UserRole->get_id_users($id_user,$relationship_type);
    ...
}
function get_id_users ($id_agency = null,$type = null) {
    $params_1 = array(
        'conditions' => array('UserRole.user_id' => $id_agency)
    );
    $user_projects = $this->UserRole->find('all',$params_1);
    $projects = array();
    for ($i = 0; !empty($user_projects[$i]); $i++) {
        $projects[] = $user_projects[$i]['UserRole']['project_id'];
    }
    $clients = array();
    foreach ($projects as $project) { //pega o id de todos os usuarios que sao clientes da lista de projetos anteriores
        $params_2 = array(
            'conditions' => array('UserRole.project_id' => $project, 'UserRole.role' => $type) 
        );
        $client_project = $this->UserRole->find('first',$params_2);
        if ($id_agency != $client_project['UserRole']['user_id']) { $clients[] = $client_project['UserRole']['user_id']; } // voce nao pode ser cliente de voce mesmo
    }
    return $clients;
}

ERROR:

 Warning (512): SQL Error: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'get_id_users' at line 1
DboSource::showQuery() - CORE/cake/libs/model/datasources/dbo_source.php, line 684
DboSource::execute() - CORE/cake/libs/model/datasources/dbo_source.php, line 266
DboSource::fetchAll() - CORE/cake/libs/model/datasources/dbo_source.php, line 410
DboSource::query() - CORE/cake/libs/model/datasources/dbo_source.php, line 364
Model::call__() - CORE/cake/libs/model/model.php, line 502
Overloadable::__call() - CORE/cake/libs/overloadable_php5.php, line 50
UserRole::get_id_users() - [internal], line ??
UsersController::get_related_clients() - APP/controllers/users_controller.php, line 71
Dispatcher::_invoke() - CORE/cake/dispatcher.php, line 204
Dispatcher::dispatch() - CORE/cake/dispatcher.php, line 171
Object::requestAction() - CORE/cake/libs/object.php, line 95
include - APP/views/elements/client_list.ctp, line 2
View::_render() - CORE/cake/libs/view/view.php, line 731
View::element() - CORE/cake/libs/view/view.php, line 392
include - APP/views/projects/index.ctp, line 45
View::_render() - CORE/cake/libs/view/view.php, line 731

UPDATE: If I call the function through requestAction it works. :(

Answer 1

You're trying to call get_id_users function on UserRole model, while it appears to be in UserRoles controller. Hence the error.

Update:

This line:

UserRole::get_id_users() - [internal], line ??

in your log clearly shows that PHP did not find function get_id_users() in UserRole class (which should be model judging by its name, but since you've mistaken the names from the very start its hard to tell without actually looking at your files).

Conclusion: rebake your app (at least User and UserRole models and controllers), doing naming right this time.

Answer 2

The answer was: I can't call functions from another controller, unless it's declared with the model. :)