get data from 3rd associated table in cakephp 3.x

Question

Ho to do recursion in cakephp 3.x

I have 4 tables

users

----------
|id|name |
|1 | mick|
----------

responses
-------------------------
id|question_id| response|
1 |      1    | slim    |   
-------------------------

questions
------------------
id   |  question  |
1    | body type  |
-------------------

user_respopnses
----------------------------------------------
id  |  user_id  | question_id  | response_id |
1   |  1        |  1           |    1        |
----------------------------------------------

following query is auto generated by Cakephp to fetch data

$users = $this->Users->get($id, [
    'contain' => ['Responses' ]    
    ]);

Response by running above query

Id   Question Id       Response         Actions
1        1               slim      View Edit Delete

I needs to display question attribute(text) instead of question Id but stuck.

Relation defined in User Table

    $this->table('users');
    $this->displayField('id');
    $this->primaryKey('id');
    $this->addBehavior('Timestamp');

    $this->belongsToMany('Responses', [
        'foreignKey' => 'user_id',
        'targetForeignKey' => 'response_id',
        'joinTable' => 'users_responses'
    ]);
}

after debugging I got this data

'responses' => [
        (int) 0 => object(App\Model\Entity\Response) {

            'id' => (int) 1,
            'question_id' => (int) 1,
            'response' => 'slim',
            '_joinData' => object(Cake\ORM\Entity) {

                'response_id' => (int) 1,
                'id' => (int) 1,
                'user_id' => (int) 1,
                'question_id' => (int) 1,
                '[new]' => false,
                '[accessible]' => [
                    '*' => true
                ],
                '[dirty]' => [],
                '[original]' => [],
                '[virtual]' => [],
                '[errors]' => [],
                '[invalid]' => [],
                '[repository]' => 'UsersResponses'

            },

Actually, User has relation with Responses table and no association with Questions table, but we can get them from 3rd table "users_responses" as this table has question_id, user_id, reponse_id. but cakephp 3.x does not allow recursion. Right now query hitting Response table and users_response table only. Any suggestion or hint to solve this issue much be appreciated.

Following SQL query to achieve result what I want (I used user_id = 4 static value as It was run on mysqlyog )

SELECT users.id , questions.question, responses.response 
FROM questions, responses, users_responses , users
WHERE
users.id = 4 AND

   users_responses.user_id = 4 AND users_responses.response_id = responses.id
   AND
   users_responses.question_id = responses.question_id

   AND
   responses.question_id = questions.id;

This database design will store questions and answers where User can choose right answers for himself and saved in "users_responses". These tables are for dating site. — coder

Greg Schmidt Greg Schmidt · Accepted Answer · 2016-08-18T18:04:18

"Recursion" isn't quite the right word here, but what you want is

$users = $this->Users->get($id, [
    'contain' => ['Responses' => ['Questions']]    
]);

This is pretty clearly described in the Eager Loading Associations portion of the manual.

get data from 3rd associated table in cakephp 3.x

1 Answers