Cakephp how can I fetch same table association data

Question

I have a database table called miles, miles data looks like after fetch and return in 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",
    },

]

I have written code to fetch data looks

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

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

Now I'm trying to change this Json structure like below

"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",
            },
            ...
        ] 
    },

]

For change Json Structure like above I have tried below codes , but don't know what is the procedure I will follow to get this output.

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

For this query getting error " "Unable to load Miles association. Ensure foreign key in Miles is selected."

My first query is Am I in right track ? Which procedure I can follow to get my desire output Json ?

I have tried by group by and order

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

Output That I'm getting

"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",
        },
        ....
 ];

No if I use Collection

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

I'm getting my desire result, but problem is pagination, can I use pagination on collection $query data ? Or how can I implement pagination here ?

@AD7six I don't know why I'm getting not found exception after complete page + 1. image : https://ibb.co/dLcmh4S .With a limit of 100 dates returned good now. I'm actually trying to create an API where per day wise I have to display user gains miles in mobile app. In group by I'm getting day,month field in per row. Should I manage it in php end ? — Abu Sayed, Jul 07 '22 at 02:54
re paginate: sorry, some bad info from me there. It will [throw a not found exception](https://github.com/cakephp/cakephp/blob/65c4bd5622ba781f6bc6e51125490fa870b3a20f/src/Controller/Component/PaginatorComponent.php#L203-L207) if you [request a page that doesn't exist](https://github.com/cakephp/cakephp/blob/f43b3f58680ae869fb2e9fa56e65406cd1250702/src/Datasource/Paging/NumericPaginator.php#L190-L195). I wasn't expecting you to be doing that, and suggest not to try and catch that - it's user error to arrive there. — AD7six, Jul 07 '22 at 08:13
**Why do you want your data in this format specifically? With a limit of 100 are you expecting 100 records or 100 dates returned?** with the first you'd receive an unbound number of records per page, With the second you'd e.g. have the same date split across different pages so there are problems/tradeoffs with each approach. Please edit the question to address, the prior comment mentioning this isn't clear. — AD7six, Jul 07 '22 at 08:15

Alimon Karim · Answer 1 · 2022-07-07T13:47:09.783

0

First you can select your group by start_date, then you can write a method in your Miles Entity. (Solution for postgresql)

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

then in your entity

public function _getMilesByStartTime()
{

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

}

Now In controller

foreach ($query as $entity) {

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

$userMiles array should contain your data ! Have n't test but think it will help !

edited Jul 07 '22 at 13:47

answered Jul 07 '22 at 13:39

Alimon Karim

4,354
10
43
68

`then in your entity` please do not suggest entity methods that emit a query - requesting the property of an entity emitting db queries like that is unexpected and can easily cause significant confusion. It also leads to weird situations like debugging an object emitting queries you don't see. – AD7six Jul 07 '22 at 14:11
@AD7six Thanks for your review. Are you proposing model layer query rather than entity layer in this scenario ? – Alimon Karim Jul 07 '22 at 18:31
In any/every scenario :) – AD7six Jul 07 '22 at 20:19

Cakephp how can I fetch same table association data

1 Answers1