Table Column Summary using Quantity

I have the following table column and 'Sum' working in the attached screenshot. My code for this is as follows:

Tables\Columns\TextColumn::make('price')
    ->money()
    ->summarize(Sum::make()->money()->label('Total Price')),

Tables\Columns\TextColumn::make('price')
    ->money()
    ->summarize(Sum::make()->money()->label('Total Price')),

My problem is that the sum doesn't take into each row's quantity. How can I make the sum take each row's quantity into account? For example, I need to mulitply each row's price * it's quantity then sum all of those. Thanks for any insight into this!

4 Replies

LeandroFerreira•14mo ago

This?

Tables\Columns\TextColumn::make('price')
    ->summarize(Summarizer::make()
        ->label('Total')
        ->using(fn (\Illuminate\Database\Query\Builder $query) => $query->sum(DB::raw('quantity * price'))))

Tables\Columns\TextColumn::make('price')
    ->summarize(Summarizer::make()
        ->label('Total')
        ->using(fn (\Illuminate\Database\Query\Builder $query) => $query->sum(DB::raw('quantity * price'))))

ShavikOP•14mo ago

Getting quite close. Had forgotten that my quantity column is a 'string'. Might be able to switch that to integer. One of those fun projects where lots of external system integrations so the data you get, isn't always ideal. Tried adding some casts in there but it doesn't seem to like those much Hmm doesn't appear to be due to my quantity column as I still get the error with ->using(fn (\Illuminate\Database\Query\Builder $query) => $query->sum(DB::raw("price"))) SQLSTATE[42601]: Syntax error: 7 ERROR: zero-length delimited identifier Hmm so I think I see the problem at the end of the query

SELECT
  sum(price) AS aggregate
FROM
  (
    SELECT
      "line_items".*
    FROM
      "line_items"
      INNER JOIN "invoices" ON "invoices"."id" = "line_items"."invoice_id"
    WHERE
      "invoices"."requisition_id" = 221
  ) AS ""

SELECT
  sum(price) AS aggregate
FROM
  (
    SELECT
      "line_items".*
    FROM
      "line_items"
      INNER JOIN "invoices" ON "invoices"."id" = "line_items"."invoice_id"
    WHERE
      "invoices"."requisition_id" = 221
  ) AS ""

current code:

Tables\Columns\TextColumn::make('price')
    ->summarize(
        Summarizer::make()
            ->label('Total')
            ->using(fn (\Illuminate\Database\Query\Builder $query) => $query->sum(DB::raw("price")))
    )

Tables\Columns\TextColumn::make('price')
    ->summarize(
        Summarizer::make()
            ->label('Total')
            ->using(fn (\Illuminate\Database\Query\Builder $query) => $query->sum(DB::raw("price")))
    )

I think I have identified a bug in Filament from this on Postgres 'bug'

awcodes•14mo ago

Are you sure posstgres isn’t the ‘bug’ lol. Just kidding. 😜

ShavikOP•14mo ago

https://tenor.com/view/spongebob-database-postgre-pgsql-gif-24506696

Tenor

Gaming

Programming

Table Column Summary using Quantity