Association data in sub array

Need Help
1

New to cakephp 3.
I have 3 tables A, B and C. And the relation is
A hasMany B , B hasMany C. I have to apply the query

SELECT A., B.,C.* FROM A
INNER JOIN B ON A.id = B.a_id AND B.active = 1
INNER JOIN C ON C.b_id = B.id AND C.active = 1
WHERE A.active = 1

I need to show the results in the following way.

A title1
    B title1
       C title1.1
       C title1.2
A title2
    B title2
       C title2.1
       C title2.2

The problem is with the result array (or object). I could able to get the data with “matching” function which is same as the above query. But the entire output is coming in single array. Is there any way to get the output in the nested format like

$result = ['A' => 
                        ['title' => 'title1', '
                        'B' => [ 
                          [
                             'title' => 'title1,
                             'C' => [
                                 ['title' => 'title1.1'],
                                 ['title' => 'title1.1'],
                              ] 
                          ]
                  ] ;

Now, since its inner join I am getting a single array element for each C row.
Any solution ?

2

Can you paste the code you have for the query and the relations between the tables?

Seems something that can be achieved easily with relations and use of contain.

$query->contain['B' => ['C']];

foreach ($query as $result) {
    //$result is an Entity
}
3

Relation
In Model A

$this->hasMany('B', [
    'foreignKey' => 'a_id',
]);

In Model B

$this->hasMany('C', [
	'foreignKey' => 'b_id'
]);

Find function is given as

`$this->find('all')->join([
                    'B' => [
                        'table' => 'b',
                        'alias' => 'B',
                        'type' => 'INNER',
                        'conditions' => [
                        	'B.a_id = A.id ',
				            'B.customer_id' => $customerId,
				            'B.status' => 'ACTIVE'
				        ]
                    ],
                    'C' => [
                        'table' => 'c',
                        'alias' => 'C',
                        'type' => 'INNER',
                        'conditions' => [
                            'C.b_id = B.id ',
                            'C.status' => 'ACTIVE',
                        ]
                    ]
                ])->where(['A.status' => 'ACTIVE'])
                ->select([
	                	'B.id', 
	                	'B.title', 
	                	'B.customer_id', 
	                	'B.a_id', 
	                	'B.status', 
	                	'C.id',
	                	'C.b_id',
	                	'C.title',
	                	'C.status'
                	])
                ->order(['A.display_order' => 'ASC'])`

And getting output as

`[
	(int) 0 => object(App\Model\Entity\A) {

		'id' => (int) 1,
		'title' => 'A title1',
		'display_order' => (int) 1,
		'status' => 'ACTIVE',
		'B' => [
			'id' => '46',
			'customer_id' => '1',
			'a_id' => '1',
			'status' => 'ACTIVE',
			'title' => 'B title 1'
		],
		'C' => [
			'id' => '88',
			'b_id' => '46',
			'title' => 'C Title 1.1',
		],
	},
	(int) 1 => object(App\Model\Entity\A) {

		'id' => (int) 3,
		'title' => 'A title2',
		'display_order' => (int) 3,
		'status' => 'ACTIVE',
		'B' => [
			'id' => '47',
			'customer_id' => '1',
			'a_id' => '3',
			'status' => 'ACTIVE'
			'title' => 'B title 2'
		],
		'C' => [
			'id' => '89',
			'b_id' => '47',
			'title' => 'C Title 2.1',
			'status' => 'ACTIVE'
		],
	
	},
	(int) 2 => object(App\Model\Entity\A) {

		'id' => (int) 19,
		'title' => 'A title 3',
		'display_order' => (int) 19,
		'status' => 'ACTIVE',
		'B' => [
			'id' => '44',
			'customer_id' => '1',
			'a_id' => '19',
			'status' => 'ACTIVE',
			'title' => 'B title 3'
		],
		'C' => [
			'id' => '84',
			'b_id' => '44',
			'title' => 'C Title 3.1',
			'status' => 'ACTIVE'
		],
	},
	(int) 3 => object(App\Model\Entity\A) {

		'id' => (int) 19,
		'title' => 'A title 3',
		'display_order' => (int) 19,
		'status' => 'ACTIVE',
		'B' => [
			'id' => '44',
			'customer_id' => '1',
			'a_id' => '19',
			'status' => 'ACTIVE',
			'title' => 'B title 3'
		],
		'C' => [
			'id' => '85',
			'b_id' => '44',
			'title' => 'C Title 3.2',
			'status' => 'ACTIVE'
		],
	}
]`

Here you can see, for the last 2 records A and B values are same. Instead of this as a seperate element I want B and C in a nested way.

4

If you define relations between tables, the easiest way to obtain related data is using contain. If you add joins manually the ORM will have no clue if you expect the data nested or not.

Check the doc for contain here: http://book.cakephp.org/3.0/en/orm/query-builder.html#loading-associations

If for some reason you have to go with the joins, then you must parse the data somehow after executing the query.