MySql Multi-table Join Query using Cakephp 3

Author - Parminder Kaur
7.11.2017
|
0 Comments
|

Below is a mysql query joining three tables.

SELECT c.course_path, t.sf_name FROM courses c JOIN modules m ON c.id = m.course_id JOIN topics t ON m.id = t.module_id WHERE t.topic_name = ‘test_topic’.

This Query will return Course_path and Sf_name from Topics Table.

In cakephp 3, we can write the same as below:

$courses = $this->courses
->find()
->select(‘course_path’)
->matching('modules.topics', function(\Cake\ORM\Query $q) {
return $q
->select(['Topics.sf_name'])
->where(['Topics.topic_name’ => ‘test_topic’]);
});

If one has to pass the data dynamically to the query using ORM Query method then one can do it using ‘use’ method and pass parameters to the use method of ORM

Example:

->matching('modules.topics', function(\Cake\ORM\Query $q) use ($topicName) {
return $q
->select(['Topics.sf_name'])
->where(['Topics.topic_name’ => $topicName]);

}

Webner Solutions is a Software Development company focused on developing Insurance Agency Management Systems, Learning Management Systems and Salesforce apps. Contact us at dev@webners.com for your Insurance, eLearning and Salesforce applications.

Leave a Reply

Your email address will not be published. Required fields are marked *