How to join multiple tables using CakePHP 3?

17
votes

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']);
        }]);
3
phpmysqlcakephpjoincakephp-3.0
You are looking for containments, not manual joins. book.cakephp.org/3.0/en/orm/… - ndm
@ndm Thank you for the response. I have updated my question with the results using containments, any suggestions? - Wisd0m
Have a closer look at how to define nested associations. - ndm

3 Answers

21
votes

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

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.

-3
votes

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