How to get the data from three tables with join in cakephp?

Question

How to get the data with common package id from three tables with join in cakephp?

package id is common between three tables.

I want to get data from three tables with particular package id.

Is there any query to fetch the data for particular package id from three tables at signle time?

Specify your table names with their Schema / table fields – Pratik Feb 17 '15 at 07:20 — Pratik, Feb 17 '15 at 07:20

score 2 · Answer 1 · edited May 23 '17 at 12:25

2

There are two main ways that you can do this. One of them is the standard CakePHP way, and the other is using a custom join.

CakePHP Join Multiple Table

edited May 23 '17 at 12:25

Community

1
1

answered Feb 17 '15 at 16:56

Supravat Mondal

2,509
1
20
32

Pratik · Answer 2 · 2015-02-17T07:19:32.323

Joins can be done as follows , substitute your tables instead of mine

Suppose here my table , controller is CourseLessonsReference Controller , so I can join other 2 or many tables as follows:

$conditionForId = array('CourseLessonsReference.course_id'=>$package_id,
    );  


    //Pagination logic
    $this->paginate = $allConditionsFields = array('conditions' => $conditionSearchLessonsByCourse,                                                                                                 
                            "joins" => array(
                                            array(//UserCourse = Course Join
                                                "table" => "courses",
                                                "alias" => "Course1",
                                                "type" => "INNER",
                                                "conditions" => array(
                                                    "Course1.id = CourseLessonsReference.course_id"
                                                )
                                            ),//For Category = Course Join
                                            array(
                                                "table" => "course_categories",
                                                "alias" => "CourseCategory",
                                                "type" => "INNER",
                                                "conditions" => array(
                                                    "CourseCategory.id = Course1.course_category_id"
                                                )
                                            )
                                        ),
                            'recursive' =>  2                               
                            );


 $resultOfJoin = $this->CourseLessonsReference->find('all', $allConditionsFields);

$resultOfJoin will give you result for Joins of 3 tables.

It means join Current table CourseLessonsReference with specified tables in Join tag in $allConditionsFields array .

As you didnt provide schema of your tables , Here in my case , Current Controller/model/table is CourseLessonsReference which will have references. References are associated with Course so course_id is foreign key in course_lessons_reference table . So this is 1 join. Now Course is connected to Category so , course will have course_category_id as foreign key.

How to get the data from three tables with join in cakephp?

2 Answers2