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,

CodePudding user response：

Laravel is about working with the Laravel 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 em to objects, all ways are ok, but i feel this is cleanest and shortest.

$analytics = DB::table('analytics')->...;

$topNine = $analytics->slice(0, 9);

$otherSum = $analytics->slice(9)->sum(function ($analytic) {
    return moneyStringToFloat($item->cogs);
});

$topNine->push((object)[
    'sku' => 'Other',
    'cogs' => floatToMoneyString($otherSum),
]);

return $topNine;

I feel like your code was actually pretty fine, i cleaned it up and used a more Laravel approach, hope you can find inspiration in it.

As a bonus, you can use analytics as a model, create an Eloquent accessor. This can make your sum into this nice syntaxic sugar using Higher Order Functions on the Collection methods.

class Analytic extends Model
{
    protected function cogsPrice(): Attribute
    {
        return Attribute::make(
            get: fn ($value) => moneyStringToFloat($this->cogs),
        );
    }
}

$topNine = $analytics->slice(0, 9);

$topNine->push((object)[
    'sku' => 'Other',
    'cogs' => floatToMoneyString($analytics->slice(9)->sum->cogsPrice),
]);

return $topNine;

CodePudding user response：

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.

$all = Analytics::orderBy('amount', 'DESC');

$merged = collect($all->take(9)->get(['sku', 'amount'])->toArray())
    ->merge(collect(array(['sku' => 'other', 'amount' => $all->skip(9)->sum('amount')])));

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:

$top9 = Analytics::orderBy('amount', 'DESC')->take(9);
$other = collect(array(['sku' => 'other', 'amount' => Analytics::whereNotIn('sku', $top9->pluck('sku'))->sum('amount')]));

$merged = collect($top9->get(['sku', 'amount'])->toArray())
        ->merge($other);

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.

CodePudding user response：

You can use take() or limit() here like this:

$data = DB::table('analytics')
            ->whereBetween('order_date', [$start, $end])
            ->whereIn('source', $suppliers)->select()
            ->orderBy('amount', 'DESC')
            ->groupBy('sku')
            ->get()
            ->take(9);

OR

$data = DB::table('analytics')
            ->whereBetween('order_date', [$start, $end])
            ->whereIn('source', $suppliers)->select()
            ->orderBy('amount', 'DESC')
            ->groupBy('sku')
            ->limit(9)
            ->get();

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 the limit(n) once the query finds the number of record equal to this it will simple stop executing making query run faster.

Syntax:

limit(9)->get() // correct
get()->limit(9) // incorrect

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 to limit() 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:

take(9)->get() // correct
get()->take(9) // correct

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:

$topNine = $data->take(9)->toArray()
$other = $data->skip(9)->take(PHP_INT_MAX)->toArray() // Some very large number to take all the remaining records PHP_INT_MAX in that case

Here skip() basically skips any number of elements you want to exclude in $other. you can skip(count($topNine)). Hope this makes thing easy to understand for you.