Pagination using joining multiple models

manpreet Best Answer 3 years ago

Each exam has one syllabus, each syllabus has one exam. So, I did this in the Exam model:

public $hasOne = 'Syllabuses'; //table: syllabuses, model: Syllabuses

From UsersController I did this:

public $uses = array('Setting', 'Exam', 'Syllabuses');

And in a method in UsersController I wanted to call paginate:

$options = array(
    'fields' => array('Exam.id', 'Exam.name', 'Syllabuses.id', 'Syllabuses.name', 'Syllabuses.syllabus', 'Syllabuses.last_updated'),
    'joins' => array(
        'table' => 'syllabuses',
        'conditions' => array('Exam.id = Syllabuses.exam_id')
    )
);
$this->paginate = $options;
$this->set('syllabuses', $this->Paginator->paginate('Syllabuses'));

exams table:

---+------+
id | name |
---+------+

and syllabuses table:

---+---------+------+----------+--------------+
id | exam_id | name | syllabus | last_updated |
---+---------+------+----------+--------------+

So, I got some error. Like this:

Error: SQLSTATE[42000]: Syntax error or access violation: 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 'syllabuses Array LEFT JOIN oes.syllabuses AS Syllabuses ON (Syllabuses.`' at line 1

And my SQL that CakePHP prepared is:

SELECT `Exam`.`id`, `Exam`.`name`, `Syllabuses`.`id`, `Syllabuses`.`name`, `Syllabuses`.`syllabus`, `Syllabuses`.`last_updated` 
FROM `oes`.`exams` AS `Exam` syllabuses Array 
LEFT JOIN `oes`.`syllabuses` AS `Syllabuses` ON (`Syllabuses`.`exam_id` = `Exam`.`id`)
WHERE 1 = 1 LIMIT 20

But what I want is something like the query bellow. I have tested it in mysql:

SELECT  `Exam`.`id` AS eid,  `Exam`.`
                                                

                                                                                                        
                                                    
                                                
                                                

                                                    

                                                        
                                                            0
                                                            views 

                                                            0
                                                                likes 
                                                            
                                                            

                                                            0
                                                            shares
                                                            


    

         Facebook
        
         Twitter
        
        
         Linked In 
        
          WhatsApp