Changing the structure of results object in CakePHP 3

Question

I have a query in CakePHP that returns the data object in the 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 I want to do is change the structure so that it is split by contract_id, and certain fields relating to its children are calculated, like 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, I want to group the results by contract_id and calculate the latest end_date from all of its children.

I have been able to use mapReduce to get the results grouped by contract_id like 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 I cannot figure out how to add calculated fields to it. I am not very experienced with mapReduce - I suppose I would need to loop through the data in order to get the largest value but I am not sure how to go about it.

I have also had a look at the formatResults method but I am not sure if that is what I should be using.

Thanks,

Kez

EDIT - REQUESTED INFORMATION:

I simplified the results structure in my initial question as I spent quite a lot of time trying to build a query that didn't end up looping multiple times for no reason. It would be nice to start the query in Contracts and contain ContractsTitles but the ORM just doesn't seem to like that, so it seemed to my that my best option was to restructure the results once I could get all the information I need. Obviously I could just manually iterate through the results and build the array I want but I feel like there must be a better way of doing this.

This is the basic structure of the tables being used:

ContractsTitles hasOne Reversions
ContractsTitles hasOne Publications
ContractsTitles belongsTo Contracts
ContractsTitles belongsTo Titles
ContractsTitles hasMany Royalties

And this is the code in the model for the 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']);

Answer 1

You could try using ->toArray() after a query.

I heard that is not very indicated to be used but could be your desired solution.

Here is some info to what I am referring: http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#finding-key-value-pairs