I'm stuck trying to create nested multiple object json arrays with group by birth_place in my rest api app, this is my code in controller:

$db = db_connect();
$data = [
    'status' => true,
    'error' => null,
    'data' => $db->query('SELECT birth_place, COUNT(id) as jumlah_data_user, GROUP_CONCAT(id) as id, GROUP_CONCAT(name) as name, GROUP_CONCAT(birth_date) as birth_date, GROUP_CONCAT(gender) as gender FROM users GROUP BY birth_place')->getResultArray()
];
return $this->respond($data, 200);

and this is my result, the data is successfully grouped but the combined data is not separated into multidimension arrays with create new title as users, but instead becomes a combined string.

"status": true,
"error": null,
"data": [
          {
           "birth_place": "Administrasi Jakarta Barat",
           "jumlah_data_user": "3",
           "id": "716,764,922",
           "name": "Elvina Nuraini,Rina Kezia Novitasari,Viktor Firmansyah M.Pd",
           "birth_date": "1975-08-28,1988-06-07,1989-05-13",
           "gender": "female,female,male"
          },
          {
           "birth_place": "Administrasi Jakarta Pusat",
           "jumlah_data_user": "2",
           "id": "993,866",
           "name": "Mursinin Banawa Marbun M.Pd,Ibrahim Gunawan",
           "birth_date": "1991-12-27,1995-01-01",
           "gender": "male,male"
          },
         ];

this result i was expecting when project running :

"status": true,
"error": null,
"data": [
    {
        "birth_place"     : "Administrasi Jakarta Barat",
        "jumlah_data_user" : 3,
        "users": [
            {
                "id": 716,
                "name": "Elvina Nuraini",
                "birth_date": "1975-08-28",
                "gender": "female"
            },
            {
                "id": 764,
                "name": "Rina Kezia Novitasari",
                "birth_date": "1988-06-07",
                "gender": "female"
            },
            {
                "id": 922,
                "name": "Viktor Firmansyah M.Pd",
                "birth_date": "1989-05-13",
                "gender": "male"
            }
        ]
    },
    {
        "birth_place": "Administrasi Jakarta Pusat",
        "jumlah_data_user" : 2,
        "users": [
            {
                "id": 993,
                "name": "Mursinin Banawa Marbun M.Pd",
                "birth_date": "1991-12-27",
                "gender": "male"
            },
            {
                "id": 866,
                "name": "Ibrahim Gunawan",
                "birth_date": "1995-01-01",
                "gender": "male"
            }
        ]
    },
];

How to do that?

CodePudding user response：

To restructured your grouped data, you need to iterate the rows then explode and transpose the last four columns containing delimited strings. (Demo)

$data = array_map(
    fn($row) => [
        'birth_place' => $row['birth_place'],
        'jumlah_data_user' => $row['jumlah_data_user'],
        'users' => array_map(
            fn(...$col) => array_combine(['id', 'name', 'birth_date', 'gender'], $col),
            explode(',', $row['id']),
            explode(',', $row['name']),
            explode(',', $row['birth_date']),
            explode(',', $row['gender'])
        )
    ],
    $this->Users_model->getGroupedByBirthPlace();
);

That said, there is some important advice to offer:

1. In a MVC framework, we should not see database queries executed from the controller. The controller's job for this task is to collect data from the model (where a method there will collect database data) and then return the response payload.

2. Grouping and concatenating in the SQL is not advisable for this task.

   • There are data length limits to these concatenated strings,
   • when values contain commas, then there is a delimiter conflict, and
   • you are unnecessarily increasing the workload for the database.

   It would make your model method more re-reusable if you made the result set less niched. A good model has methods that can be used more than once throughout the project.

3. Try not to mix English with Malay (or other non-English languages). The project will be easier to manage and review if the language is consistent. I'll suggest group_total instead of jumlah_data_user.

With a simpler payload coming from your model's query, there is no need to make iterated explode&transpose processes. This is the more professional way to design your application. (Demo)

$grouped = array_values(
    array_reduce(
        $this->Users_model->get(), // call the method whatever you like
        function($data, $row) {
            $group = $row['birth_place'];
            $data[$group] ??= [
                'birth_place' => $row['birth_place'],
                'group_total' => 0
            ];
              $data[$group]['group_total'];
            unset($row['birth_place']);
            $data[$group]['users'][] = $row;
            return $data;
        }
    )
);