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']);
        }]);

Solution 1

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'
        ]);

Solution 2

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.

Comments

[ndm]

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...

[fuzzy dunlop]

->contain(['Topics.Posts.Users' i had no idea you could do that, i spent a whole day trying to figure that out.

[Parixit]

I was not aware that join() method is there. Thanks for pointing.

[rahim.nagori]

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 ?