在CakePHP 3中更改结果对象的结构

Question

我在CakePHP中有一个查询，它返回以下结构的数据对象：

[
  { 
    "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"
  }
]

我想要做的是更改结构，使其按contract_id拆分，并计算与其子元素相关的某些字段，如下所示：

[
  {
    "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"
      }
    ]
  }
]

总之，我希望按contract_id对结果进行分组，并计算其所有子对象的最新end_date。

我已经能够使用mapReduce获得按contract_id分组的结果，如下所示：

  $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);

但是我不知道如何向其中添加计算字段。我对mapReduce不是很有经验-我想我需要遍历数据才能得到最大的值，但我不确定该怎么做。

我也看过formatResults方法，但我不确定我是否应该使用它。

谢谢,

Kez

编辑-请求的信息：

我在最初的问题中简化了结果结构，因为我花了相当多的时间来尝试构建一个不会无缘无故地多次循环的查询。在Contracts中开始查询并包含ContractsTitles会很好，但ORM似乎不喜欢这样，所以在我看来，我最好的选择是在获得所需的所有信息后重新组织结果。显然，我可以手动迭代结果并构建我想要的数组，但我觉得肯定有更好的方法来做到这一点。

这是正在使用的表的基本结构：

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

以下是模型中用于查询的代码：

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