Laravel DB::raw() to take dynamic inputs-CodePudding

I have query where I am getting the sum of price by suppliers. The below query works fine.

    private function integrationsSpendBySupplier(array $suppliers)
    {
        $totalBySupplier = DB::table('analytics')
            ->whereIn('source', $suppliers)->select(
                'source',
                DB::raw('sum(price) as total')
            )
            ->groupBy('source')
            ->get();

        return [
            'title' => 'Spend per Supplier',
            'rows'  => 12,
            'type'  => 'bar',
            'data'  => [
                'labels'   => $totalBySupplier->map(fn ($supplier) => $supplier->source),
                'datasets' => [
                    [
                        'label' => 'Total Spending',
                        'data'  => $totalBySupplier->map(fn ($supplier) => $this->stringToFloat($supplier->total))
                    ],
                ]
            ],
            'hasToolTip' => true
        ];
    }

However, I would also like to add more items to the DB::raw() call like so:

DB::raw('sum(price   shipping   tax   something_else) as total')

But these values will be dynamic so it could be all or none of additional params (price will always be there though).

Any thoughts?

CodePudding user response：

You can build the SUM() string using basic PHP string and apply that string to the query.

for example

$price = ''; 
if (priceRequired())
  $price = "price ";     

$shipping = '';
if (requiresShipping())
  $shipping  = ", shipping";

// $shipping = requiresShipping() ? ", shipping" : ""; if you're familiar with this syntax 

... 

DB::raw("sum($price $shipping ...etc) as total");

as a result, the final string should be like price, shipping, tax or price depending on the above conditions.
Note: be careful about the comma's place in the string, this may cause query exceptions