Filament•16mo ago

Custom Query Returns getTableRecordKey()

I have been trying to make the following data display using table widgets . as an example 1. Please note that each line of is actually different customer , i just need to display the Product Name and Total Sales of each product. 2. Link that i have reffered to: https://github.com/filamentphp/filament/discussions/3182 & https://stackoverflow.com/questions/24887708/laravel-eloquent-sum-with-groupby Product Name | Price Product 1 | 500 Product 1 | 300 Product 1 | 200 Product 2 | 250 Product 2 | 300 Instead of price should just show a total of sales on each product like follow: Product 1 | 1,000 Product 2 | 550 Here are some code that i have tried:

 
<?php

namespace App\Filament\Widgets;


use App\Models\TradingProfile;
use Filament\Tables;
use Filament\Widgets\TableWidget as BaseWidget;
use Illuminate\Database\Eloquent\Builder;
use Filament\Tables\Columns\TextColumn;
use Illuminate\Support\Facades\DB;


class TradingProfileSales extends BaseWidget
{

    protected static ?int $sort = 2;

    protected function getTableHeading(): string|null
    {
        return 'Product Sales';
    }

    protected function getTableQuery(): Builder
    {
    return TradingProfile::selectRaw('product_id, SUM(price) as total_price')
        ->groupBy('product_id');
    }

    protected function getTableColumns(): array
    {
        return [
            TextColumn::make('product.name')
                ->label('Product Name'),
            TextColumn::make('total_price')
                ->label('Total Sales'),
        ];
    }

    protected function isTablePaginationEnabled(): bool
    {
        return false;
    }


}

 
<?php

namespace App\Filament\Widgets;


use App\Models\TradingProfile;
use Filament\Tables;
use Filament\Widgets\TableWidget as BaseWidget;
use Illuminate\Database\Eloquent\Builder;
use Filament\Tables\Columns\TextColumn;
use Illuminate\Support\Facades\DB;


class TradingProfileSales extends BaseWidget
{

    protected static ?int $sort = 2;

    protected function getTableHeading(): string|null
    {
        return 'Product Sales';
    }

    protected function getTableQuery(): Builder
    {
    return TradingProfile::selectRaw('product_id, SUM(price) as total_price')
        ->groupBy('product_id');
    }

    protected function getTableColumns(): array
    {
        return [
            TextColumn::make('product.name')
                ->label('Product Name'),
            TextColumn::make('total_price')
                ->label('Total Sales'),
        ];
    }

    protected function isTablePaginationEnabled(): bool
    {
        return false;
    }


}

GitHub

Updated Filament: getTableRecordKey() Prevents Display Of Aggregate...

Just updated Filament forms & table builder to latest version, and one of my dashboard doesn't work anymore. I believe the problem is because this particular dashboard shows aggregated repo...

Stack Overflow

Laravel Eloquent: sum with groupBy

Need eloquent/fluent query to get sum with groupBy function. So far I have tried: $this->data['no_of_pages'] = Document::sum('no_of_pages') ->groupBy('

3 Replies

Patrick Boivin•16mo ago

I don't think you can do a groupBy like this in getTableQuery... Or maybe you're just missing some columns like id in your selectRaw().

kodekodimas•16mo ago

have you tried this docs? https://filamentphp.com/docs/2.x/tables/columns/relationships#aggregating-relationships

Filament

Column relationships - Columns - Table Builder - Filament

The elegant TALL stack table builder for Laravel artisans.

alcmzOP•16mo ago

Hi Guys, Sorry for the late reply . It's possible however , it seems like there are some more parameters that i needed for the query Thanks did look into it but some parameters were missing when i applied the SUM on price . this is what i missed:

     COALESCE(FORMAT(SUM(price), 2), "0.00") as total_price,

     COALESCE(FORMAT(SUM(price), 2), "0.00") as total_price,

Gaming

Programming

Custom Query Returns getTableRecordKey()