I’m new to PHP so my solution might be very inefficient so asking here to figure out something more efficient/better.
Consider an array of objects:
[
['sku' => 'AAA', 'amount' => ###],
['sku' => 'BBB', 'amount' => ###],
['sku' => 'CCC', 'amount' => ###],
['sku' => 'DDD', 'amount' => ###],
['sku' => 'EEE', 'amount' => ###],
['sku' => 'FFF', 'amount' => ###],
['sku' => 'GGG', 'amount' => ###],
['sku' => 'HHH', 'amount' => ###],
['sku' => 'III', 'amount' => ###],
['sku' => 'JJJ', 'amount' => ###],
['sku' => 'KKK', 'amount' => ###],
['sku' => 'LLL', 'amount' => ###],
['sku' => 'MMM', 'amount' => ###],
]
We want to keep the first 9 as they are, but consolidate the remaining under the 'sku' => 'Other'
and some the amount.
Here is the working version of the code:
$data = DB::table('analytics')
->whereBetween('order_date', [$start, $end])
->whereIn('source', $suppliers)->select(
[
'sku',
DB::raw('SUM(analytics.price' . ($costs ? ' + ' . $costs : '') . ') as amount'),
]
)
->orderBy('amount', 'DESC')
->groupBy('sku')
->get();
$dataArray = $data->toArray();
$topNine = array_slice($dataArray, 0, 9);
$other = array_slice($dataArray, 9);
if (count($other)) {
$otherSum = array_reduce($other, function ($carry, $item) {
return $carry += moneyStringToFloat($item->cogs);
}, 0);
$otherObj = new stdClass();
$otherObj->sku = 'Other';
$otherObj->cogs = floatToMoneyString($otherSum);
$topNine[] = $otherObj;
}
And the final result looks something like this:
[
['sku' => 'AAA', 'amount' => ###],
['sku' => 'BBB', 'amount' => ###],
['sku' => 'CCC', 'amount' => ###],
['sku' => 'DDD', 'amount' => ###],
['sku' => 'EEE', 'amount' => ###],
['sku' => 'FFF', 'amount' => ###],
['sku' => 'GGG', 'amount' => ###],
['sku' => 'HHH', 'amount' => ###],
['sku' => 'III', 'amount' => ###],
['sku' => 'Other', 'amount' => ###],
]
Is there a better way to do this. Is there a way to do it directly in QueryBuilder?
Thank you,
3
Answers
Laravel
is about working with theLaravel
Collection
and all the methods it provides.Firstly, you don’t have to convert it to an array, work with the data as is. Collections has a slice method, and instead of reducing it has a sum method which does the same as you are doing. So instead of juggling between arrays,
PHP
functions etc. keep it simple, shorter and Laravelistic. Creating a default object in PHP, can be done multiple ways, I like to create arrays and cast them to objects, all methods are OK, but I feel this is cleanest and shortest.Your code was fine, I cleaned it up and used a more
Laravel
approach, hope you get inspiration from it.As a bonus, you can use analytics as a model, create an Eloquent accessor. This can make your sum into this nice syntactic sugar using Higher Order Functions on the Collection methods.
Depending on the size of the data you’re returning and what you intend to do with that data (i.e. do you need it all later?) you could return all the data and then manipulate it in memory.
Alternatively, if you’re only interested in the first 9 individual records and everything from the 10th record onward is of no interest and you don’t require them later for any logic, you could get the first 9 and then everything else:
The above option means not loading a potentialy large data set into memory and performing the limiting and summing operation on the database, but does require some additional calls to the database. So there is a trade off to be taken into consideration with these.
You can use
take()
orlimit()
here like this:OR
The Difference
Although they both do pretty much the same thing there’s some difference worth knowing in them.
limit()
limit()
only works on eloquent ORM or query builder objects. which means that the number n specified in thelimit(n)
once the query finds the number of record equal to this it will simple stop executing making query run faster.Syntax:
take()
take()
take will simply let the query run until the records are fetched and then it simply extracts the number of records specified, making it slower as compared tolimit()
but it has it’s own uses such as having the count of all records but taking only few records as in your case.Syntax:
Your Case
Since you want all the records here’s what you can do is, once the data is fetched in $data, you can simply:
Here
skip()
basically skips any number of elements you want to exclude in$other
. you canskip(count($topNine))
.Hope this makes thing easy to understand for you.