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