orm - Changing the structure of results object in CakePHP 3 -

i have query in cakephp returns data object in following structure:

[   {      "join_id": 1,     "contract_id": 1,     "title": "title",     "sales": 500,     "earnings": 50,     "publisher": "publisher",     "end_date": "2016-11-15"   },   {      "join_id": 2,     "contract_id": 1,     "title": "title",     "sales": 500,     "earnings": 50,     "publisher": "publisher",     "end_date": "2016-01-15"   },   {      "join_id": 3,     "contract_id": 2,     "title": "title",     "sales": 500,     "earnings": 50,     "publisher": "publisher",     "end_date": "2016-05-15"   } ] 

what want change structure split contract_id, , fields relating children calculated, this:

[   {     "contract_id": 1,     "end_date": "2016-11-15",     "joins": [       {         "join_id": 1,         "title": "title",         "sales": 500,         "earnings": 50,         "publisher": "publisher",         "end_date": "2016-11-15"       },       {         "join_id": 2,         "title": "title",         "sales": 500,         "earnings": 50,         "publisher": "publisher",         "end_date": "2016-01-15"       }     ]   },   {     "contract_id": 2,     "end_date": "2016-05-15",     "joins": [       {         "join_id": 3,         "title": "title",         "sales": 500,         "earnings": 50,         "publisher": "publisher",         "end_date": "2016-11-15"       }     ]   } ] 

in summary, want group results contract_id , calculate latest end_date of children.

i have been able use mapreduce results grouped contract_id so:

  $mapper = function ($contractstitle, $key, $mapreduce) {       $contract_id = $contractstitle->contract_id;       $mapreduce->emitintermediate($contractstitle, $contract_id);   };    $reducer = function ($contractstitle, $contract_id, $mapreduce) {       $mapreduce->emit($contractstitle, $contract_id);   };    $query->mapreduce($mapper, $reducer); 

but cannot figure out how add calculated fields it. not experienced mapreduce - suppose need loop through data in order largest value not sure how go it.

i have had @ formatresults method not sure if should using.

thanks,

kez

edit - requested information:

i simplified results structure in initial question spent quite lot of time trying build query didn't end looping multiple times no reason. nice start query in contracts , contain contractstitles orm doesn't seem that, seemed my best option restructure results once information need. manually iterate through results , build array want feel there must better way of doing this.

this basic structure of tables being used:

contractstitles hasone reversions contractstitles hasone publications contractstitles belongsto contracts contractstitles belongsto titles contractstitles hasmany royalties 

and code in model query:

$subquery = $this->contracts->royalties   ->find()   ->contain(['contracts'])   ->where(['contracts.publisher_id' => $options['publisher_id']]);  $subquery   ->select(['contract_id' => 'royalties.contract_id','title_id' => 'royalties.title_id','sold' => $subquery->func()->sum("sold"), 'earned' => $subquery->func()->sum("earned")])   ->group(['royalties.title_id','royalties.contract_id']);  $query = $this->contracts->contractstitles   ->find()   ->select(['contracts.id', 'contracts.date', 'titles.title', 'reversions.date', 'publications.date', 'sold' => 'r.sold', 'earned' => 'r.earned'])   ->where(['contracts.publisher_id' => $options['publisher_id']])   ->contain(['contracts','titles','reversions','publications'])   ->join([     'table' => $subquery,     'alias' => 'r',     'type' => 'left',     'conditions' => ['r.contract_id = contractstitles.contract_id','r.title_id = contractstitles.title_id']   ])   ->order(['contracts.date desc, publications.date desc']); 

you try using ->toarray() after query.

i heard not indicated used desired solution.

here info referring: http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#finding-key-value-pairs