How to make join in cakephp

Question

I try to get the result of student (last insert).

I can easily fetch the record with mysql query like.

 SELECT student.first_name,student.last_name,result.* 
    FROM result 
    join student 
        on student.student_id=result.student_id 
 WHERE result.id IN (
            SELECT MAX(result.id) FROM result GROUP BY result.student_id
        )

I try it to convert it in cake-php like this with pagination

$this->paginate = array(
    'fields' => array(
        'Student.first_name',
        'Student.last_name',
        'Result.*',
    ),
    'joins' => array(

        array(
            'conditions' => array(
                'Student.student_id = Result.student_id',
            ),
            'table' => 'student',
            'alias' => 'Student',
            'type' => 'join',
        ),
    ),
    'conditions' => array(
        'Result.id' => array(
        ),
    ),
    'limit'=>10,
    'contain' => array(
        'Student',
    ),
);
$data = $this->paginate('Result');

http://stackoverflow.com/questions/5079908/cakephp-find-method-with-join — Aditya Vyas-Lakhan, Sep 29 '15 at 08:16
That first query is finding students and their last result record? That's not going to be a very efficient query, would make more sense to have `students.last_result_id` and update it whenever a new result is created, for example. You need to say what version of CakePHP you are using (Tag the question). — AD7six, Sep 29 '15 at 08:17
if you want only last insert result then why you are passing in paginate. just write a query for `$this->model->find('first'`. — urfusion, Sep 29 '15 at 08:50

score 0 · Answer 1 · answered Sep 30 '15 at 07:28

Please try this

$this->paginate = array(
    'fields' => array(
        'Student.first_name',
        'Student.last_name',
        'Result.*',
    ),
    'joins' => array(
        array(
            'conditions' => array(
                'Student.student_id = Result.student_id',
            ),
            'table' => 'student',
            'alias' => 'Student',
            'type' => 'join',
        ),
    ),
    'conditions' => array(
        'Result.id IN ( SELECT MAX(result.id) FROM result GROUP BY result.student_id )
    ),
    'limit'=>10
);
$data = $this->paginate('Result');

How to make join in cakephp

1 Answers1