How do JOIN using find()?

Question

I am trying to Join tables using cakephp find(). I have 3 tables, they are: users, pessoas e matriculas. On the table users i have a foreign key for table pessoas and in the table matriculas I have a foreign key for pessoas.

I want to do a JOIN between these tables, I’m trying but when I do JOIN for the table matriculas the return comes empty. I don’t know what can be if I’m doing wrong. How to do this ?

I’m trying like this.

public function doLogin(){
            $this->autoRender = false;
            $json = $this->request->input("json_decode", true);
            $email = $json["User"]["email"];
            $senha = $json["User"]["senha"];

            $sql["conditions"] = array(
                "User.email"=>$email,
                "User.senha"=>$senha,
                "User.status"=>1
            );
            $sql["joins"] = array(
                array(
                    "table"=>"matriculas",
                    "alias"=>"m",
                    "type"=>"INNER",
                    "conditions"=>array("Pessoa.id"=>"m.pessoas_id")
                )
            );

            $users = $this->User->find('all', $sql);

            $array;
            if($users){
                $array = array("status"=>"1", "msg" => "Login efetuado", "result"=>$users);
            }else{
                $array = array("status"=>"0", "msg" => "Usuário ou senha inválido", "result"=>$users);
            }

            return json_encode($array);
        }

Empty result

{"status":"0","msg":"Usu\u00e1rio ou senha inv\u00e1lido","result":[]}

my ERM project

Answer 1

The way you model this Join, is from the version before 2.x. x!

Correct would be this way:

...

$conditions = array(

    'conditions' =>  array(

        'User.email' => $email,
        'User.senha' => $senha,
        'User.status' => 1

    ),

    'joins' => array(

        array(
            'table' => 'matriculas',
            'alias' => 'm',
            'type' => 'INNER',
            'conditions' => array(
                'Pessoa.id' => 'm.pessoas_id'
            )
        ),

    ),

);


$users = $this->User->find('all', $conditions);

...