Site icon FSIBLOG

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

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

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.

$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;
}
if ($ld->output_final > 0) {
    $ld->downtime_batch = round(($ld->output / $ld->output_final) * 100, 2);
} else {
    $ld->downtime_batch = 0;
}
$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.

$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.

$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.

Exit mobile version