Computed column in Value / Trend Metrics

So I'm attempting to add a Value metric that will sum the value of (quantity * price) in an order_lines table.

I've tried things like

    public function calculate(NovaRequest $request)
    {
        return $this->sum($request, OrderLine::select(\DB::raw("SUM(quantity * price) as total")), 'total')
            ->format(['thousandSeparated' => true])
            ->currency('$');
    }

An exception is thrown that SQL engine can't find total in the field list.

I've also attempted to try and add a mutator to the OrderLines model and then build the Value like:

public function calculate(NovaRequest $request)
    {
return $this->sum($request, OrderLine::class, 'total')
            ->format(['thousandSeparated' => true])
            ->currency('$');
}

Where total is the mutator on the OrderLine model. The obvious option is to add a 'total' field that is computed when the line saves and then have the Value run against it.

I'm hoping I'm just missing something obvious, syntactically. Thanks for any direction you can give!

bobbybouwmann

6 years ago

Best Answer

Level 88

The problem here is that the sum method only performs direct queries to get the results. So using a mutator or a calculate column won't work! Luckily there is a workaround

public function calculate(NovaRequest $request)
{
    $orderLine = OrderLine::selectRaw('SUM(quantity * price) as total');

    return (new \Laravel\Nova\Metrics\ValueResult($orderLine->total))
        ->format(['thousandSeparated' => true])
        ->currency('$');
}

Something like this should work for you. Basically you're building your own ValueResult object that can be used to return the value you want to show. The value itself is just a value, so you can perform your own query and return that.

tim.roenicke

6 years ago

Level 5

Thank you for taking the time to answer my question @bobbybouwmann!

This has set me on the right path!

I had assumed it would use a hydrated model (or collection of) like much of the framework does. As i was digging into the underlying aggregate() I started to realize this wasn't the case. This was all an attempt to keep all of their related previousRange() and currentRange() magic. I'll just apply this to the approach you provided.

Thanks again!

bobbybouwmann

6 years ago

Level 88

Yeah, all the metrics only use direct queries.

FrazeColder

5 years ago

Level 1

@bobbybouwmann I get Property total does not exist on the Eloquent builder instance. Do you know why that? I am using Laravel 8 and Nova 3.

I do exactly the same thing except my Model is called Product. I want to sum up the link_clicks_general and link_clicks_details.

public function calculate(NovaRequest $request)
    {
        $product = Product::selectRaw('SUM(link_clicks_general + link_clicks_details) as total');

        return (new \Laravel\Nova\Metrics\ValueResult($product->total))
            ->format(['thousandSeparated' => true])
            ->currency('$');
    }

PartTimer

5 years ago

Level 1

I had a similar issue, and resolved it by getting the first result back from the base query, see below:

public function calculate(NovaRequest $request)
    {
        $product = Product::selectRaw('SUM(link_clicks_general + link_clicks_details) as total')
		->toBase() // get the query rather than model
		->first(); // only one row for an aggregate

        return (new \Laravel\Nova\Metrics\ValueResult($product->total))
            ->format(['thousandSeparated' => true])
            ->currency('$');
    }

Please or to participate in this conversation.