How to join multiple tables using CakePHP 3?

Question

I am using CakePHP 3.x.

What I want is to be able to call $this->Categories->find() and then join Topics on Topics.cat_id = Categories.id and then join Posts on Posts.topic_id = Topics.id.

I don't get any errors but the only data I'm getting back is the Categories data, i have tried LEFT and INNER join with no success. Any help would be greatly appreciated.

The table relationships are:

CategoriesTable

$this->hasMany('Topics');

TopicsTable

$this->belongsTo('Categories');
$this->hasMany('Posts');

PostsTable

$this->belongsTo('Topics');

Also the query i have:

$query = $this->Categories->find('all')
        ->order(['Categories.name' => 'ASC'])
        ->join([
            'topics' => [
                'table' => 'Topics',
                'type' => 'LEFT',
                'conditions' => 'topics.Cat_id = Categories.id'
            ],
            'posts' => [
                'table' => 'Posts',
                'type' => 'LEFT',
                'conditions' => 'posts.topic_id = topics.id'
            ]
        ]);

Tried using the containable behavior but now i'm getting the error "Categories is not associated with Posts" using this query:

$query = $this->Categories->find('all')
        ->order(['Categories.name' => 'ASC'])
        ->contain(['Topics', 'Posts' => function($q){
            return $q->where(['Posts.topic_id' => 'Topics.id']);
        }]);

You are looking for containments, not manual joins. **http://book.cakephp.org/3.0/en/orm/query-builder.html#loading-associations** — ndm, May 24 '15 at 01:53
@ndm Thank you for the response. I have updated my question with the results using containments, any suggestions? — Wisd0m, May 24 '15 at 02:34

score 21 · Accepted Answer · answered May 24 '15 at 20:51

21

With the advice @ndm gave i was able to get the result i wanted. I must have overlooked the section in the docs, so anyone else having this problem, here's how to do it.

$query = $this->Categories->find('all')
        ->order(['Categories.name' => 'ASC'])
        ->contain([
            'Topics.Posts.Users'
        ]);

answered May 24 '15 at 20:51

Wisd0m

521
1
5
14

->contain(['Topics.Posts.Users' i had no idea you could do that, i spent a whole day trying to figure that out. – fuzzy dunlop Jul 23 '16 at 18:30
How to get data from three table ? For example I have three table : A, B, C. I need to left join all the three table. How to do that ? – rahim.nagori Jul 10 '19 at 12:46

score 3 · Answer 2 · answered May 07 '16 at 04:33

Just find the kind of legacy of cakephp2, you can still use join like the old version.

$result = $this->Category->findAll(fields=>['id','Topic.id'], 'conditions'=>['Topic.name'=>'s'],'join'=>['Topic' => [
            'table' => 'topics',
            'type' => 'INNER',
            'conditions' => 'Topic.category_id = Category.id'
        ]]);

Find familiar? you still be able to use alias as before

First time answer, not sure how the code editor works, sorry.

I was not aware that `join()` method is there. Thanks for pointing. — Parixit, Jun 23 '17 at 06:44

score -3 · Answer 3 · answered May 25 '15 at 08:59

Try this:

 $query = $this->Categories->find('all')
        ->fields('Categories.*', 'Topics.*', 'Posts.*')
        ->order(['Categories.name' => 'ASC'])
        ->join([
            'topics' => [
                'table' => 'Topics',
                'type' => 'LEFT',
                'conditions' => 'topics.Cat_id = Categories.id'
            ],
            'posts' => [
                'table' => 'Posts',
                'type' => 'LEFT',
                'conditions' => 'posts.topic_id = topics.id'
            ]
        ]);

Add this line:

->fields('Categories.*', 'Topics.*', 'Posts.*')

to retrieve data from Topics and Posts

This won't work for many reasons, there is no method named `fields()`, choosing fields is done via `select()`, however `.*` isn't supported by the ORM query builder, it will be aliased and result in invalid SQL, also additional fields on non contained associations are not going to be hydrated, and you'll get duplicate results as there are 1:n relationships, etc... — ndm, May 25 '15 at 12:23

How to join multiple tables using CakePHP 3?

3 Answers3