Group Data from a SELECT using the DATE parameter

Question

I have the following scenario:

I am getting a SELECT, where each record contains the coordinate and creation date of this Record. I would like to display by DATE the amount of coordinates obtained from that DAY.

Example of SELECT:

[{"id":"2","dataCriacao":"2021-03-17 ","latlng":"-23.550829,-46.2008791"},
{"id":"9","dataCriacao":"2021-03-17 ","latlng":"-23.5507955,-46.2008757"},
{"id":"11","dataCriacao":"2021-03-17 ","latlng":"-23.5582206,-46.1995978"},
{"id":"6","dataCriacao":"2021-03-15 ","latlng":"-23.5508117,-46.2008772"},
{"id":"7","dataCriacao":"2021-03-15 ","latlng":"-23.550829,-46.2008791"},
{"id":"8","dataCriacao":"2021-03-15 ","latlng":"-23.550855,-46.2008978"},
{"id":"1","dataCriacao":"2021-03-14 ","latlng":"-23.550829,-46.2008791"}]

I would like to display on the basis of DATE example :

Data : 2021-03-17 Coordenadas : -23.550829,-46.2008791/-23.5507955,-46.2008757/-23.5582206,-46.1995978
Data : 2021-03-15 Coordenadas : -23.5508117,-46.2008772/-23.550829,-46.2008791/-23.550855,-46.2008978
Data : 2021-03-14 Coordenadas : -23.550829,-46.2008791

But I’m not sure how to accomplish this concatenation. I am getting the data via SELECT in PHP and displaying in Javascript and HTML.

Could help me give you a Light of what resource to use in this case?

Answer 1

Could group in the PHP or in the javascript.

In the javascript could group using Reduce.

"Date : 2021-03-17 Coordinates : -23.550829,-46.2008791/-23.5507955,-46.2008757/-23.5582206,-46.1995978" this format should be an example (such as json it is invalid), can use the reduce to group by date and associate an array with coordinates generating a new json with valid format, for example:

var dados = [
   {
      "id":"2",
      "dataCriacao":"2021-03-17 ",
      "latlng":"-23.550829,-46.2008791"
   },
   {
      "id":"9",
      "dataCriacao":"2021-03-17 ",
      "latlng":"-23.5507955,-46.2008757"
   },
   {
      "id":"11",
      "dataCriacao":"2021-03-17 ",
      "latlng":"-23.5582206,-46.1995978"
   },
   {
      "id":"6",
      "dataCriacao":"2021-03-15 ",
      "latlng":"-23.5508117,-46.2008772"
   },
   {
      "id":"7",
      "dataCriacao":"2021-03-15 ",
      "latlng":"-23.550829,-46.2008791"
   },
   {
      "id":"8",
      "dataCriacao":"2021-03-15 ",
      "latlng":"-23.550855,-46.2008978"
   },
   {
      "id":"1",
      "dataCriacao":"2021-03-14 ",
      "latlng":"-23.550829,-46.2008791"
   }
];

var result = dados.reduce(function (acumulador, item) {
    var key = item["dataCriacao"];

    // verifica se já agrupou pela data, senão cria um novo objeto
    if (!acumulador[key]) {
      acumulador[key] = { "coordenadas":  [] };
    }
    
    // adiciona à data, propriedade "coordenadas" o valor de latlng
    acumulador[key].coordenadas.push(item.latlng);
    return acumulador;
  }, {});

// resultado agrupado
console.log(result);