Cakephp如何获取相同的表关联数据

我有一个数据库表叫miles，miles数据在Json中取数和返回后的样子。

"userMiles": [
    {
         "id": 278,
         "gain_miles": 0.02,
         "start_time": "2022-07-06T15:40:47+09:00",
         "end_time": "2022-07-06T15:45:08+09:00",
    },

    {
         "id": 279,
         "gain_miles": 0.02,
         "start_time": "2022-08-06T15:40:47+09:00",
         "end_time": "2022-08-06T15:45:08+09:00",
    },

]

我写了代码来获取数据

$milesTable = TableRegistry::getTableLocator()->get( 'Miles' );
$query = $milesTable->find('all'));

try{
    $userMiles = $this->paginate($query);
}
catch (NotFoundException $e){
    $userMiles = [];
}

现在，我尝试更改此Json结构，如下所示

"userMiles": [
    {
        "start_date": "06-July",
        "miles":[
            {
                "id": 278,
                "gain_miles": 0.02,
                "start_time": "2022-07-06T15:40:47+09:00",
                "end_time": "2022-07-06T15:45:08+09:00",
            },
            ...
        ] 
    },

    {
        "start_date": "01-Mar",
        "miles":[
            {
                "id": 281,
                "gain_miles": 0.20,
                "start_time": "2022-03-01T15:40:47+09:00",
                "end_time": "2022-03-01T15:45:08+09:00",
            },
            ...
        ] 
    },

]

对于像上面这样的更改Json结构，我已经尝试了下面的代码，但不知道我将遵循什么程序来获得此输出。

$query = $milesTable->find();
    ->select(["start_date" => "TO_CHAR(start_time,'DD-Mon')"])
    ->contain('Miles')
;

In `milesTable.php` 

$this->hasMany('Miles',[
        'foreignKey' => 'start_time',
        'joinType' => 'LEFT',
]);

对于此查询，获取错误““无法加载Miles关联。请确保选择了Miles中的外键。”
我的第一个问题是我是否在正确的轨道上？我可以遵循哪个过程来获得我想要的输出Json？
我试过按组按顺序

$query = $milesTable
    ->find()
    ->select([
        "start_date" => "TO_CHAR(start_time,'DD-Mon')",
        "gain_miles",
        "start_time",
        "end_time",
    ])
    ->group(['start_date','id'])
    ->order(['start_time' => 'DESC'])
;

我得到的输出

"query": [
        {
            "start_date": "05-Jul",
            "gain_miles": 400,
            "start_time": "2022-07-05T14:14:23+06:00",
            "end_time": "2022-07-06T14:14:23+06:00",
        },
        ....
 ];

如果我使用集合，则为否

$collection = new Collection($query);
$miles = $collection->groupBy('start_date');

我得到了我想要的结果，但是问题是分页，我可以在集合$query数据上使用分页吗？或者我如何在这里实现分页？

首先，您可以通过start_date选择您的组，然后可以在Miles实体中编写一个方法。（postgresql的解决方案）

$query = $this->paginate($milesTable
      ->find()
      ->select([
          "start_date" => "TO_CHAR(start_time,'DD-Mon')",
      ])
      ->group(['start_date'])
);

那么在你的实体中

public function _getMilesByStartTime()
{

        return \Cake\ORM\TableRegistry::getTableLocator()->get('Miles')
        ->find()
        ->where([
            "TO_CHAR(start_time,'DD-Mon') = '$this->start_date'"
        ]);

}

现在输入控制器

foreach ($query as $entity) {

    $userMiles[] = [
        'start_time' => $entity->start_date,
        'miles' => $entity->milesByStartTime
    ];
}

$userMiles array应该包含您的数据！没有测试过，但认为它会有帮助！

Cakephp如何获取相同的表关联数据

1条答案

相关问题

热门标签

最新问答