Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Create multidimensional structure from grouped aggregate data from an Eloquent query

I want to create a nested JSON object, something like this:

[
    {
        "age": 8,
        "countAll": 3,
        "gender": [
            {
                "genderName": "male",
                "countGender": 2
            },
            {
                "genderName": "female",
                "countGender": 1
            },
        ]
    },
    {
        "age": 10,
        "countAll": 1,
        "gender": [
            {
                "genderName": "male",
                "countMale": null (or "0")
            },
            {
                "genderName": "female",
                "countFemale": 1
            },
        ]
    },

I have one table in mysql:

| id | name     |  gender  | age | user_id | 
--------------------------------------------
| 1  | nameA    | male     | 8   |    1    |
| 2  | nameB    | female   | 10  |    1    |
| 3  | nameC    | male     | 8   |    1    |
| 4  | nameD    | female   | 8   |    1    |

I am using laravel 10, this is the query I did

$test = Participant::select('age', DB::raw('COUNT(age) as countAge'), DB::raw('COUNT(gender) as  countGender'))->where('user_id', $user_id)->groupBy('age')->orderBy('age', 'ASC')->get();

$output = array();
$currentAge = "";
$currentCount = "";

foreach ($test as $data) {
   if ($data->age != $currentAge) {
      $output[] = array();

      end($output);
      $currentItem = &$output[key($output)];
      $currentAge = $data->age;
      $currentCount = $data->countAge;
      $currentItem['age'] = $currentAge;
      $currentItem['countAll'] = $currentCount;
      $currentItem['gender'] = array();
    }
    $currentItem['gender'][] = array('genderName' => $data->gender, 'countGender' => $data->countGender);
}

and json_encoded result:

[
    {
        "age": 8,
        "countAll": 3,
        "gender": [
            {
                "genderName": null,
                "countGender": 3
            }
        ]
    },
    {
        "age": 10,
        "countAll": 1,
        "gender": [
            {
                "genderName": null,
                "countGender": 1
            }
        ]
    },

I have read a lot of references regarding create nested JSON and made many changes to the SQL query but so far I haven’t found a solution.

How can I make this Eloquent query return a nested JSON object like the one I describe above?

Tags:

2

Answers


  1. 0

    Something similar can be done like below:

    $participants = Participant::select('age')
    ->selectRaw('COUNT(*) as countAll')
    ->selectRaw('SUM(CASE WHEN gender = "male" THEN 1 ELSE 0 END) as countMale')
    ->selectRaw('SUM(CASE WHEN gender = "female" THEN 1 ELSE 0 END) as countFemale')
    ->where('user_id', $user_id)
    ->groupBy('age')
    ->orderBy('age', 'ASC')
    ->get();

$result = [];

foreach ($participants as $participant) {
    $genderData = [
        [
            'genderName' => 'male',
            'countGender' => $participant->countMale ?? 0,
        ],
        [
            'genderName' => 'female',
            'countGender' => $participant->countFemale ?? 0,
        ]
    ];

    $result[] = [
        'age' => $participant->age,
        'countAll' => $participant->countAll,
        'gender' => $genderData,
    ];
}

$jsonOutput = json_encode($result);
    Login or Signup to reply.
  2. 0

    I find your desired output structure to be a little unwieldy. It might make better sense to declare the deeper gender-specific data points as simpler associative elements genderCounts => ['male' => 2, 'female' => 1]. In fact, to avoid the convolution of collection method calls on the result set, you should flatten your desired result to a 2d structure so that it can be purely achieved with SQL.

    Code: (PHPize Demo)

    $user_id = 1;

var_export(
    DB::table('Participant')
    ->select('age')
    ->selectRaw('COUNT(1) total')
    ->selectRaw("SUM(gender = 'male') male")
    ->selectRaw("SUM(gender = 'female') female")
    ->where('user_id', $user_id)
    ->groupBy('age')
    ->orderBy('age', 'ASC')
    ->get()
    ->toArray()
);

    to prepare that aggregate data with a Laravel collection method, just chain map() after the get() call. (PHPize Demo)

    ->map(fn($row) => [
    'age' => $row->age,
    'countAll' => $row->total,
    'gender' => [
        ['genderName' => 'male', 'countMale' => $row->male],
        ['genderName' => 'female', 'countFemale' => $row->female]
    ]
])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search