Sum (quantity * price)

Hello, Tried for hours to get a total for orders , there's something I'm doing wrong?

public function items()
    {
        return $this->hasMany(OrderItem::class);
    }

Forms\Components\Placeholder::make('total')
->content(fn ($record) => $record->items->sum(\DB::raw('quantity * price')))

public function items()
    {
        return $this->hasMany(OrderItem::class);
    }

Forms\Components\Placeholder::make('total')
->content(fn ($record) => $record->items->sum(\DB::raw('quantity * price')))

or trough an attribute

  public function getTotalAttribute()
  {
    return $this->quantity * $this->price;
  }

Forms\Components\Placeholder::make('total')
->content(fn ($record) => $record->items->total)

  public function getTotalAttribute()
  {
    return $this->quantity * $this->price;
  }

Forms\Components\Placeholder::make('total')
->content(fn ($record) => $record->items->total)

25 Replies

toeknee•2y ago

So in that you are expecting the tital to come from the items class. I would suggest you have a relationship that returns itemsTotal within the record model. Otherwise use formatState using from items and loop through i.e.

Forms\Components\Placeholder::make('items_total')
->content(function ($record) { 
$total = 0;
foreach($record->items as $item) {
      $total += (float)$item->price * (float)$item->quantity;
}
return $total;
)

Forms\Components\Placeholder::make('items_total')
->content(function ($record) { 
$total = 0;
foreach($record->items as $item) {
      $total += (float)$item->price * (float)$item->quantity;
}
return $total;
)

As a rough example.

PastekoOP•2y ago

    public function total()
    {
        return  $this->hasMany(InvoiceItem::class)
            ->selectRaw('SUM(quantity * price)');
    }

    public function total()
    {
        return  $this->hasMany(InvoiceItem::class)
            ->selectRaw('SUM(quantity * price)');
    }

Thank you, it's working, how can I just access the number returned? $record->total = [{"SUM(quantity * price)":"162.00"}]

toeknee•2y ago

Adjust it to

public function total()
{
    return $this->hasMany(InvoiceItem::class)
        ->sum(DB::raw('quantity * price'));
}

public function total()
{
    return $this->hasMany(InvoiceItem::class)
        ->sum(DB::raw('quantity * price'));
}

PastekoOP•2y ago

It gives me that : App\Models\Invoice::total must return a relationship instance.

Dan Harrin•2y ago

->content(fn ($record) => $record->items()->sum(DB::raw('quantity * price'))

PastekoOP•2y ago

Thanks, it was my first try without the parentheses... How can I use that for a Table now?

Dan Harrin•2y ago

uhhhhh using a subselect on the query by extending getTableQuery()

PastekoOP•2y ago

It's possible to use something like this for table and form?

public function total()
    {
        return  $this->hasMany(InvoiceItem::class)
            ->selectRaw('SUM(quantity * price) + '.$this->shipping_price.' AS final_total');
    }

public function total()
    {
        return  $this->hasMany(InvoiceItem::class)
            ->selectRaw('SUM(quantity * price) + '.$this->shipping_price.' AS final_total');
    }

Dan Harrin•2y ago

they are different problems in the form, you only need to do it once in the table, it needs to be added to the table query otherwise you will experience an n+1 problem hence why i suggested adding a subselect statement onto the table query. have you done that in SQL before?

PastekoOP•2y ago

IN (SELECT FROM...) these are the subselects I have used before but just a few times. Added this to InvoiceRessource\Pages\ListInvoices.php

protected function getTableQuery(): Builder
  {
    return Invoice::select(
      DB::raw('ALL'))
        ->orderBy('id', 'DESC')
        ->groupBy('id');
  }

protected function getTableQuery(): Builder
  {
    return Invoice::select(
      DB::raw('ALL'))
        ->orderBy('id', 'DESC')
        ->groupBy('id');
  }

Could not check compatibility between App\Filament\Resources\InvoiceResource\Pages\ListInvoices::getTableQuery():

Dan Harrin•2y ago

https://reinink.ca/articles/dynamic-relationships-in-laravel-using-subqueries#introducing-subqueries

Dynamic relationships in Laravel using subqueries

In this article I explain how to use subqueries to create dynamic relationships in Laravel. This is a powerful technique that allows you to push more work into the database layer of your app. This can have a huge impact on performance by allowing you to drastically reduce the number of database queries executed and overall memory used.

Dan Harrin•2y ago

i would write it for you but i want you to learn so i dont have to do it again 😂

PastekoOP•2y ago

I know and you're right! Finally! The compatibility problem was solved by : use Illuminate\Database\Eloquent\Builder;

    protected function getTableQuery(): Builder
    {
        return Invoice::query()
        ->addSelect(['big_total' => InvoiceItem::selectRaw('SUM(quantity * price) + pk_invoices.shipping_price')
            ->whereColumn('invoice_id', 'pk_invoices.id')
        ]);
    }

    protected function getTableQuery(): Builder
    {
        return Invoice::query()
        ->addSelect(['big_total' => InvoiceItem::selectRaw('SUM(quantity * price) + pk_invoices.shipping_price')
            ->whereColumn('invoice_id', 'pk_invoices.id')
        ]);
    }

Sorry just two little more things : How can I refresh the Total when I add a product? Why is it adding the shipping costs 2 times?

->content(fn ($record) => $record->items()->sum(DB::raw('(quantity * price) + '.$record->shipping_price.'')))

->content(fn ($record) => $record->items()->sum(DB::raw('(quantity * price) + '.$record->shipping_price.'')))

PastekoOP•2y ago

Dan Harrin•2y ago

you need a livewire event to trigger from the relation manager and listen on the page and call $refresh

PastekoOP•2y ago

like this trick : https://filamentphp.com/tricks/update-main-form-after-relationship-manager-crud ?

Filament

Update Main form after Relationship Manager CRUD by iotron - Tricks...

Filament is a collection of tools for rapidly building beautiful TALL stack apps, designed for humans.

Dan Harrin•2y ago

yup

PastekoOP•2y ago

any clue on why is it adding shipping 2 times?

Dan Harrin•2y ago

i do not, you should debug that using dd()

PastekoOP•2y ago

ok thank you Sorry for needing your help again, but how can I get the total of all invoices with this :

return Invoice::query()
  ->addSelect(['big_total' => InvoiceItem::selectRaw('SUM(quantity * price)')
  ->whereColumn('invoice_id', 'pk_invoices.id')
   ]);

return Invoice::query()
  ->addSelect(['big_total' => InvoiceItem::selectRaw('SUM(quantity * price)')
  ->whereColumn('invoice_id', 'pk_invoices.id')
   ]);

toeknee•2y ago

Remove the whereColumn

PastekoOP•2y ago

$total = Order::where('payment_status', 1)
  ->addSelect(['big_total' => OrderItem::selectRaw('SUM(quantity * price)')  
  ]);

$total = Order::where('payment_status', 1)
  ->addSelect(['big_total' => OrderItem::selectRaw('SUM(quantity * price)')  
  ]);

$total->big_total = Property [big_total] does not exist on the Eloquent builder instance. How can I access big_total?

toeknee•2y ago

Ahh my bad. The where was right, I missed the sub select. Why not just do: $invoices = Invoice::query() ->addSelect([ 'pk_invoices.id', 'big_total' => InvoiceItem::selectRaw('SUM(quantity * price)') ->whereColumn('invoice_id', 'pk_invoices.id') ]) ->get(); $total = $invoices->sum('big_total');

PastekoOP•2y ago

Thank you lets try this 🙂 It's working now, big thanks for your help.

toeknee•2y ago

No problem

Gaming

Programming

Sum (quantity * price)