Join query retrieving data from only one table cakephp 3

Question

I am new to cakephp. I am usign query builder to fetch details from two tables using join in cakephp query builder.But the query i am writing is fetching details only from one table. Need help to get data from other table as well.

This is my code to fetch data by joining two tables:

public function edit($id = null) {

    $events_table = TableRegistry::get('Events');

    $events = $events_table->find('all') 
            ->hydrate(false)
            ->join([
            'CourseType'=>  [
                        'table'      => 'coursetype',
                        'type'       => 'LEFT',
                        'conditions' => 'Events.type = CourseType.coursetype_id',
                    ]                   
            ])->where(['Events.id' => $id]);

     $event = $events->toArray();       
     $this->set('event', $event);
}

As a result i am getting only details from events table. But i need data from coursetype also.Any help is appreciated. Thank you.

try this https://stackoverflow.com/a/845519/4514250. May be it is helpful. — Parag Soni, Jul 28 '17 at 09:01

score 1 · Accepted Answer · answered Jul 28 '17 at 09:51

1

Manually adding joins won't cause any additional data to be selected, you'd have to do that on your own by specifying the fields to be selected via the select() method.

$events = $events_table
    ->find('all') 
    ->hydrate(false)
    ->select($events_table)
    // either add the fields one by one, or pass a table object
    // as above to select all fields of the table
    ->select(['CourseType.id', 'CourseType.xyz', /* ... */])
    // ...

I'd suggest to use use containments instead, ie setup the required association if not already present, and then simply use contain():

$events = $events_table
    ->find('all') 
    ->hydrate(false)
    ->contain('CourseType')
    ->where(['Events.id' => $id]);

See also

answered Jul 28 '17 at 09:51

ndm

59,784
9
71
110

is `CourseType` model name? – Varuni N R Jul 28 '17 at 09:57
That depends on your setup, I just used the same aliases as you did in your example. If you'd follow the [**naming conventions**](https://book.cakephp.org/3.0/en/intro/conventions.html#model-and-database-conventions) of CakePHP, then the "model" (table class) name would be `Coursetype`, and if you'd follow the convetions for database table names too, then that would be `course_types`, and the model name would be `CourseTypes`. @VaruniNR – ndm Jul 28 '17 at 10:00
Okay Thank you. Will try it out – Varuni N R Jul 28 '17 at 10:08
With contain i can use join? – Varuni N R Jul 28 '17 at 10:20
No, you use `contain()` instead of `join()`. @VaruniNR – ndm Jul 28 '17 at 10:38
Your answer was useful. I got the answer. Thanks a lot. Though i did not experiment it with contain i got it using the `select()`. – Varuni N R Jul 28 '17 at 11:35

score -3 · Answer 2 · answered Jul 28 '17 at 08:52

-3

your solution here: CakePHP find method with JOIN

answered Jul 28 '17 at 08:52

Pratik Soni

78
7

I think thats for cakephp 2.x. Can you send answer for cakephp 3.x? – Varuni N R Jul 28 '17 at 09:26

Join query retrieving data from only one table cakephp 3

2 Answers2