Categories

See More
Popular Forum

MBA (4887) B.Tech (1769) Engineering (1486) Class 12 (1030) Study Abroad (1004) Computer Science and Engineering (988) Business Management Studies (865) BBA (846) Diploma (746) CAT (651) B.Com (648) B.Sc (643) JEE Mains (618) Mechanical Engineering (574) Exam (525) India (462) Career (452) All Time Q&A (439) Mass Communication (427) BCA (417) Science (384) Computers & IT (Non-Engg) (383) Medicine & Health Sciences (381) Hotel Management (373) Civil Engineering (353) MCA (349) Tuteehub Top Questions (348) Distance (340) Colleges in India (334)
See More

Pagination using joining multiple models

Course Queries Syllabus Queries
Max. 2000 characters
Replies

usr_profile.png
NeelKamal Jha

User

( 6 months 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`.`
								 								 
							usr_profile.png
							
Kajal Gaur

User

( 6 months ago )

 

Ok, I think this can be helpful for many programmers. That's why I want to share what I did finally:

$options = array(
    'fields' => array(
        'Exam.id',
        'Exam.name',
        'Syllabuses.id',
        'Syllabuses.name',
        'Syllabuses.exam_id',
        'Syllabuses.syllabus',
        'Syllabuses.last_updated'
    ),
    'recursive' => 0,
    'conditions' => array('Exam.id = Syllabuses.exam_id'),
    'limit' => 3
);
$this->paginate = $options;
$syllabuses = $this->Paginator->paginate('Exam');

what's your interest