How to GrpupBy and Sum in Widget Table.
I have widget table where I want sum some columns grouped by accoount_id and instrument_id
I don't know how to sum values like number, provision ...
->sum('number') // Dont work
I havean error:
Filament\Tables\Table::query(): Argument #1 ($query) must be of type Illuminate\Database\Eloquent\Builder|Closure|null, int given, called in C:\laragon\www\inwestycje\app\Filament\Widgets\InstrumentsInAccouts.php on line 29
class InstrumentsInAccouts extends BaseWidget
{
protected int | string | array $columnSpan = 'full';
protected static ?int $sort = 2;
public static function getEloquentQuery(): Builder
{
return static::getModel()::query()->where('is_admin', 1);
}
public function table(Table $table): Table
{
return $table
->query(
TransactionResource::getEloquentQuery()
->groupBy('account_id', 'instrument_id')
->sum('number') // Dont work
)
->defaultSort('created_at', 'desc')
->columns([
TextColumn::make('date')->sortable(),
TextColumn::make('account.name')->sortable(),
TextColumn::make('number')
->alignRight(),
TextColumn::make('instrument.name'),
TextColumn::make('price')
->money('PLN', locale: 'pl')
->alignRight(),
TextColumn::make('provision')
->money('PLN', locale: 'pl')
->alignRight()
->summarize(Sum::make()),
TextColumn::make('number')
->alignRight(),
]);
}
}
}
class InstrumentsInAccouts extends BaseWidget
{
protected int | string | array $columnSpan = 'full';
protected static ?int $sort = 2;
public static function getEloquentQuery(): Builder
{
return static::getModel()::query()->where('is_admin', 1);
}
public function table(Table $table): Table
{
return $table
->query(
TransactionResource::getEloquentQuery()
->groupBy('account_id', 'instrument_id')
->sum('number') // Dont work
)
->defaultSort('created_at', 'desc')
->columns([
TextColumn::make('date')->sortable(),
TextColumn::make('account.name')->sortable(),
TextColumn::make('number')
->alignRight(),
TextColumn::make('instrument.name'),
TextColumn::make('price')
->money('PLN', locale: 'pl')
->alignRight(),
TextColumn::make('provision')
->money('PLN', locale: 'pl')
->alignRight()
->summarize(Sum::make()),
TextColumn::make('number')
->alignRight(),
]);
}
}
}
1 Reply
Ok, it took me a while to get it to work, but it's working now
class InstrumentsInAccounts extends BaseWidget
{
protected int | string | array $columnSpan = 'full';
protected static ?int $sort = 2;
public int | string $perPage = 20;
public function table(Table $table): Table
{
return $table
->query(
TransactionResource::getEloquentQuery()
->where('number','>',0)
->groupBy('account_id', 'instrument_id')
->select(
'id',
'account_id',
'instrument_id',
'transaction_type',
DB::raw('SUM(number) as number'),
DB::raw('SUM(value) * -1 as value'),
DB::raw('AVG(price) as price')
)
)
->columns([
TextColumn::make('account.name')->sortable(),
TextColumn::make('instrument.name'),
TextColumn::make('instrument.ticker_google_finanse')
->label(__('Ticker')),
TextColumn::make('price')
->label(__('AVG Price'))
->money('PLN', locale: 'pl')
->alignRight(),
TextColumn::make('number')
->label(__('Quantity available'))
->alignRight()
->summarize(Sum::make()),
TextColumn::make('value')
->label(__('Purchase equity'))
->money('PLN', locale: 'pl')
->alignRight()
->summarize(Sum::make()->label('Total value')),
]);
}
}
class InstrumentsInAccounts extends BaseWidget
{
protected int | string | array $columnSpan = 'full';
protected static ?int $sort = 2;
public int | string $perPage = 20;
public function table(Table $table): Table
{
return $table
->query(
TransactionResource::getEloquentQuery()
->where('number','>',0)
->groupBy('account_id', 'instrument_id')
->select(
'id',
'account_id',
'instrument_id',
'transaction_type',
DB::raw('SUM(number) as number'),
DB::raw('SUM(value) * -1 as value'),
DB::raw('AVG(price) as price')
)
)
->columns([
TextColumn::make('account.name')->sortable(),
TextColumn::make('instrument.name'),
TextColumn::make('instrument.ticker_google_finanse')
->label(__('Ticker')),
TextColumn::make('price')
->label(__('AVG Price'))
->money('PLN', locale: 'pl')
->alignRight(),
TextColumn::make('number')
->label(__('Quantity available'))
->alignRight()
->summarize(Sum::make()),
TextColumn::make('value')
->label(__('Purchase equity'))
->money('PLN', locale: 'pl')
->alignRight()
->summarize(Sum::make()->label('Total value')),
]);
}
}