Grouped and detailed result in one array with pagination on grouped result

Need Help
1

Hi all

I am relatively new to CakePHP 4.40 but have done a lot of reading in the cook book, done the tutorials and read on other helpful staring guides. It’s all working so far, however am I stuck with below topic. I have done research on my own but so far could not find a solution.

I have below table and would like to select data in a way that the template can access data via one array.

Work log table:

id date hours
1 2022-07-08 2.00
2 2022-06-21 5.00
3 2022-06-06 4.00
4 2022-05-03 1.00

I can get data grouped by month and on an id level and apply paging on one of both.

$worklog = $this->Worklog
	->find()
	->select(['yearmonth' => 'concat(YEAR(date), MONTH(date))', 'month' => 'MONTH(date)', 'year' => 'YEAR(date)'])
	->group('MONTH(date)')
	->order(['date' => 'DESC']);
$this->set('worklog', $this->paginate($worklog, ['limit'=> '2']));

$worklog_detail = $this->Worklog->find()
	->select(['id', 'date', 'hours'])
	->order(['date' => 'DESC']);
$this->set('worklog_detail', $worklog_detail);

However, I can’t get the code to create below JSON. My desired array for template:

Page 1 of 2:

{
  "2022_7": {
    "month": "7",
    "year": "2022",
    "data": [
      {
        "id": "1",
        "date": "2022-07-08",
        "hours": "2.0"
      }
    ]
  },
  "2022_6": {
    "month": "6",
    "year": "2022",
    "data": [
      {
        "id": "2",
        "date": "2022-06-21",
        "hours": "5.0"
      },
      {
        "id": "3",
        "date": "2022-06-06",
        "hours": "4.0"
      }
    ]
  }
}

Page 2 of 2:

{
  "2022_5": {
    "month": "5",
    "year": "2022",
    "data": [
      {
        "id": "4",
        "date": "2022-05-03",
        "hours": "1.0"
      }
    ]
  }
}

The frontend will present data grouped by month & year with accordions.

Page 1 of 2:

Accordion 1: July 2022
1 2022-07-08 2.00

Accordion 2: June 2022
2 2022-06-21 5.00
3 2022-06-06 4.00

Page 2 of 2:

Accordion 3: Mai 2022
4 2022-05-03 1.00

I’d very much appreciate if you could give me some guidance on how this is most efficient implemented.

Many thanks in advance!

2

Hard for me to tell exactly what you are trying to accomplish but have you looked at using contains for associated data? You could also look at sub selects.

https://book.cakephp.org/4/en/orm/retrieving-data-and-resultsets.html#eager-loading-associations-via-contain

https://book.cakephp.org/4/en/orm/query-builder.html#subqueries

Or even merge the data yourself in a single array or object and return it.

3

Hi @cnizzardini, many thanks for your reply and guidance with sub selects and eager loading! I had a look at is but I am still not clear on how to achieve a paging which is not based on number of records in the table but rather on a group by function of data from the table.

Re “Hard for me to tell exactly what you are trying to accomplish”: Let me try to put it in other words. I am trying to achieve to present data in the frontend as per below screenshot.

grafik

  • Data is grouped by yearmonth. yearmonth is calculated on the fly based on the date. So for July there would be one record, for June 2 and for May 1 record. Each month can have 1 to n records.
  • Pagination is based on yearmonth (July and June on first page, May on second page) and not first two records of the work log table

I could retrieve the data in the format I need for the frontend with below query. However, I am unsure on how to apply paging on yearmonth instead of just limiting number of records.

SELECT concat(YEAR(date), MONTH(date)) AS yearmonth,
       id, date, hours
FROM worklog w1
LEFT JOIN
  (SELECT concat(YEAR(date), MONTH(date)) AS yearmonth
   FROM worklog
   GROUP BY concat(YEAR(date), MONTH(date))
   ORDER BY date DESC
   LIMIT 2) AS w2 ON concat(YEAR(date), MONTH(date)) = w2.yearmonth
WHERE concat(YEAR(date), MONTH(date)) = w2.yearmonth;

So in other words I believe following order would need to take place:

  • firstQuery= select concat(YEAR(date), MONTH(date)) from work_log group by concat(YEAR(date), MONTH(date))
  • apply pagination on result
  • secondQuery = select id, date, hours from work_log where concat(YEAR(date), MONTH(date)) = firstQuery.yearmonth

Would you have guidance on the paging in combination with my selects? Many thanks…