How in PHP, preferably compatible with Yii2 ActiveDataProvider, to select data from a table in order to group them by date in JSON?

Question

MySQL

This is what the query looks like without "grouping":

$query = $query->orderBy(['dt' => SORT_DESC, 'id' => SORT_DESC]);
return new ActiveDataProvider([
          'query' => $query,
          'pagination' => [
              ...
          ]
      ]);

Next there is RestController, this is what it outputs in the end:

{
"items": [
  {
    "id": "5",
    "dt": "2017-01-10 07:56",
    ...
  },
  {
    "id": "4",
    "dt": "2017-01-10 07:55",
    ...
  },
  {
    "id": "3",
    "dt": "2017-01-09 13:52",
    ...
  },
  ...
]
}

Everything would be fine, but I also need to group the data in JSON by date. That is, like this:

{
"items": [
  "2017-01-10": [
    {
      "id": "5",
      "dt": "2017-01-10 07:56",
      ...
    },
    {
      "id": "4",
      "dt": "2017-01-10 07:55",
      ...
    }
  ],
  "2017-01-09": [
    {
      "id": "3",
      "dt": "2017-01-09 13:52",
      ...
    }
  ]
  ...
]
}

This will be needed later in the front-end.
And here are the problems.
First, MySQL has GROUP BY, there is also a method for it in Yii2 ActiveQuery, but, as I understand it, this is not at all, that is, it does not return such a nested array, but does something else altogether.
Then I just do this:

$query = $query->orderBy(['dt' => SORT_DESC, 'id' => SORT_DESC]);
$rawArr = $query->all();

And then in the loop I manually form a new object and group everything in it as it should
. But what's next?
After all, I can’t slip a ready-made object into the ActiveDataProvider.
You can generally get rid of the ActiveDataProvider and return the object directly, but I still need pagination, it turns out that I also have to implement it myself, which is already quite inconvenient, long and fraught with SQL vulnerabilities.
How can you solve the problem - make such an object, while maintaining ActiveDataProvider support for pagination, etc.?

Answer 1

This will be needed later in the front-end.

Maybe on the "front-end" and should be collected in such an array? Why collect on the server side? Or do you also use it?

Answer 2

You cannot get the desired result through group by.
Usually this is done after receiving all the records of interest, something like:

$byDate = ArrayHelper::map($dataProvider->getModels(), 'dt', function($data) { return $data; });

Answer 3

In the simplest version, you can do something like this:

$query = $query->orderBy(['dt' => SORT_DESC, 'id' => SORT_DESC]);
$dataProvider  = new ActiveDataProvider([
    'query' => $query,
    'pagination' => [
    .....
    ]
]);
$dataProvider->models = \yii\helpers\ArrayHelper::index($dataProvider->models, null, 'dt');

But it would be more correct if you implement your data provider that inherits from the current one and returns the result in the desired form.