How to Get Data Values from a Collection Using Group By on Laravel

I’ve often found myself needing to work with collections of data and then group that data for further analysis or display. Today, I’m excited to share how I achieved this in Laravel. I’ll walk you through a complete project coding example where I first calculate values for each record, group the data by a specific column (in this case, the id), and then add some extra functionality like calculating group averages. Let’s dive into the code and the detailed explanation!

The Code

Below is the complete PHP code that I wrote to achieve this:

// Get the data from the model.
$linedata = GetMyData::select('id', 'runtime_batch', 'smallstoptime_batch', 'downtime_batch', 'output', 'output_final')->get();

// Calculate percentage values for each record.
$linedata->each(function ($ld) {
    // Calculate the total for percentage calculations.
    $total = $ld->runtime_batch + $ld->smallstoptime_batch + $ld->downtime_batch;
    
    // Avoid division by zero.
    if ($total > 0) {
        $ld->runtime_batch = round((($ld->runtime_batch + $ld->smallstoptime_batch) / $total) * 100, 2);
    } else {
        $ld->runtime_batch = 0;
    }

    // Check output_final to avoid division by zero.
    if ($ld->output_final > 0) {
        $ld->downtime_batch = round(($ld->output / $ld->output_final) * 100, 2);
    } else {
        $ld->downtime_batch = 0;
    }
    
    // For smallstoptime, recalculating using the sum of runtime and smallstoptime.
    $subTotal = $ld->runtime_batch + $ld->smallstoptime_batch;
    if ($subTotal > 0) {
        $ld->smallstoptime_batch = round(($ld->output_final / $subTotal) * 100, 2);
    } else {
        $ld->smallstoptime_batch = 0;
    }
});

// Group the calculated collection by "id"
// Each group will be a collection of items with the same id.
$groupingline = $linedata->groupBy('id')->map(function ($group) {
    return $group->values(); // reindex each group so keys start at 0
})->values();

// -- Extra Practice Functionality --
// Calculate average percentages for each group.
$groupAverages = $groupingline->map(function ($group, $id) {
    return [
        'id' => $id,
        'runtime_avg' => round($group->avg('runtime_batch'), 2),
        'downtime_avg' => round($group->avg('downtime_batch'), 2),
        'smallstoptime_avg' => round($group->avg('smallstoptime_batch'), 2),
    ];
})->values();

// Optional: Flatten the grouped data to a single-level array if needed.
$flattenedData = $groupingline->flatten(1);

// Return a JSON response with both the grouped data and the group averages.
return response()->json([
    'groupedData'   => $groupingline,  // Data grouped by id (an array of arrays)
    'groupAverages' => $groupAverages, // Extra aggregated data
    //'flatData'    => $flattenedData,   // Alternatively, use a flat list of records
]);

Step-by-Step Detailed Explanation

Data Retrieval

I started by fetching the necessary data from the GetMyData model using Laravel’s Eloquent ORM. With the select() method, I specified exactly which columns I needed. In my case, I required id, runtime_batch, smallstoptime_batch, downtime_batch, output, and output_final. Then, using get(), I retrieved all matching records from the database. This returns a Laravel collection, a very powerful tool that I can use for data manipulation.

$linedata = GetMyData::select('id', 'runtime_batch', 'smallstoptime_batch', 'downtime_batch', 'output', 'output_final')->get();

Data Calculation

Before grouping the data, I needed to perform some calculations on each record. I did this by iterating over each element in the collection with the each() method.

• Runtime Percentage Calculation:
  • What I did: I added the values of runtime_batch and smallstoptime_batch and divided that sum by the total of all three batch fields (runtime_batch, smallstoptime_batch, and downtime_batch).Why: This gives me a percentage that reflects the runtime relative to the overall batch process.Division by Zero Check: Before performing the division, I verified that the total isn’t zero to prevent any errors.

$total = $ld->runtime_batch + $ld->smallstoptime_batch + $ld->downtime_batch;
if ($total > 0) {
    $ld->runtime_batch = round((($ld->runtime_batch + $ld->smallstoptime_batch) / $total) * 100, 2);
} else {
    $ld->runtime_batch = 0;
}

• Downtime Percentage Calculation:
  • What I did: I calculated downtime as the ratio of output to output_final and multiplied the result by 100 to get a percentage.Why: This provides insight into the downtime relative to the expected output.Division by Zero Check: I added a check to make sure that output_final isn’t zero.

if ($ld->output_final > 0) {
    $ld->downtime_batch = round(($ld->output / $ld->output_final) * 100, 2);
} else {
    $ld->downtime_batch = 0;
}

• Smallstoptime Percentage Calculation:
  • What I did: I recalculated the smallstoptime_batch value using the new runtime values. I took the sum of the recalculated runtime_batch and smallstoptime_batch, then computed the percentage of output_final relative to this subtotal.Why: This gives a recalculated metric that can be compared across the records.Division by Zero Check: Similar to the previous calculations, I ensured the subtotal wasn’t zero.

$subTotal = $ld->runtime_batch + $ld->smallstoptime_batch;
if ($subTotal > 0) {
    $ld->smallstoptime_batch = round(($ld->output_final / $subTotal) * 100, 2);
} else {
    $ld->smallstoptime_batch = 0;
}

Grouping the Data

After performing the calculations, I moved on to grouping the data by the id field. This is important because I might have multiple records with the same id, and I want to organize them into a neat structure.

• Grouping Process:
  • I used Laravel’s groupBy() method to cluster records by id.I then applied the map() method along with values() to reindex each subgroup. This ensures that the keys within each group start at zero, making the data structure cleaner and easier to work with.

$groupingline = $linedata->groupBy('id')->map(function ($group) {
    return $group->values();
})->values();

Extra Functionality – Calculating Group Averages

To take my project further, I calculated the average percentage values for each group. This additional functionality provides a quick summary or overview of the data in each group.

• How I Calculated Averages:
  • For every group, I used the avg() method on the collection to compute the average for each of the three computed fields (runtime_batch, downtime_batch, and smallstoptime_batch).I rounded the averages to two decimal places for a cleaner output.

$groupAverages = $groupingline->map(function ($group, $id) {
    return [
        'id' => $id,
        'runtime_avg' => round($group->avg('runtime_batch'), 2),
        'downtime_avg' => round($group->avg('downtime_batch'), 2),
        'smallstoptime_avg' => round($group->avg('smallstoptime_batch'), 2),
    ];
})->values();

Optional Flattening

In some scenarios, you might want a single-level array rather than a nested group array. I demonstrated how you can achieve this with the flatten(1) method. This step is optional and depends on how you want to structure your final output.

$flattenedData = $groupingline->flatten(1);

Returning the Response

Finally, I return the processed data as a JSON response. I include both the grouped data and the aggregated averages so that consumers of the API can easily access detailed records along with summary information.

return response()->json([
    'groupedData'   => $groupingline,  // Data grouped by id (an array of arrays)
    'groupAverages' => $groupAverages, // Extra aggregated data
    //'flatData'    => $flattenedData,   // Alternatively, use a flat list of records
]);

Final Thoughts

I’m really happy with how this approach turned out. By performing the calculations on the collection first and then grouping the data, I ensured that all transformations were applied correctly before aggregation. The extra functionality of calculating group averages not only makes the code more robust but also adds a layer of insightful data analysis to the final output.

Working with Laravel collections in this way has given me a deeper appreciation for the framework’s flexibility and power when it comes to data manipulation. I hope you find this example as useful as I did, and that it inspires you to explore further enhancements and optimizations in your own projects.