Filament•15mo ago

Custom getTableQuery() shows bad information after create record.

Hello, I have a custom query on ListPage to make groupBy and sum() instructions on the table. After create a record shows bad information but after refresh page it shows correctly Thanks in advance!

<?php

namespace App\Filament\Resources\InventoryResource\Pages;

use App\Filament\Resources\InventoryResource;
use App\Models\Inventory;
use Filament\Pages\Actions;
use Filament\Resources\Pages\ListRecords;
use Illuminate\Database\Eloquent\Builder;

class ListInventories extends ListRecords
{
    protected static string $resource = InventoryResource::class;

    protected function getActions(): array
    {
        return [
            Actions\CreateAction::make()
                ->slideOver(),
        ];
    }

    protected function getTableQuery(): Builder
    {
        //  Return all inventories records but group by asset_id and sum the quantity
        return Inventory::query()
            ->join('assets', 'assets.id', '=', 'inventories.asset_id')
            ->join('locations', 'locations.id', '=', 'inventories.location_id')
            ->selectRaw('
                random() as id,
                asset_id,
                location_id,
                sum(quantity) as quantity,
                assets.name as asset_name,
                locations.name as location_name
            ')
            ->groupByRaw('
                asset_id,
                location_id,
                asset_name,
                location_name
            ');
        //
    }
}

<?php

namespace App\Filament\Resources\InventoryResource\Pages;

use App\Filament\Resources\InventoryResource;
use App\Models\Inventory;
use Filament\Pages\Actions;
use Filament\Resources\Pages\ListRecords;
use Illuminate\Database\Eloquent\Builder;

class ListInventories extends ListRecords
{
    protected static string $resource = InventoryResource::class;

    protected function getActions(): array
    {
        return [
            Actions\CreateAction::make()
                ->slideOver(),
        ];
    }

    protected function getTableQuery(): Builder
    {
        //  Return all inventories records but group by asset_id and sum the quantity
        return Inventory::query()
            ->join('assets', 'assets.id', '=', 'inventories.asset_id')
            ->join('locations', 'locations.id', '=', 'inventories.location_id')
            ->selectRaw('
                random() as id,
                asset_id,
                location_id,
                sum(quantity) as quantity,
                assets.name as asset_name,
                locations.name as location_name
            ')
            ->groupByRaw('
                asset_id,
                location_id,
                asset_name,
                location_name
            ');
        //
    }
}

6 Replies

gon.exeOP•15mo ago

    public static function table(Table $table): Table
    {
        return $table
            ->columns([

                Tables\Columns\TextColumn::make('asset_name')
                    ->label(__('Asset'))
                    ->searchable()
                    ->sortable(),

                Tables\Columns\TextColumn::make('location_name')
                    ->label(__('Location'))
                    ->searchable()
                    ->sortable(),

                Tables\Columns\TextColumn::make('Asset.unitMeasure.name')
                    ->label(__('Unit Measure'))
                    ->searchable()
                    ->sortable()
                    ->toggleable(isToggledHiddenByDefault: true),

                Tables\Columns\TextColumn::make('quantity')
                    ->label(__('Quantity'))
                    ->searchable()
                    ->sortable()
                    ,//->suffix(fn ($record) => ' '.$record->Asset->unitMeasure->name),

                Tables\Columns\TextColumn::make('created_at')
                    ->label(__('Created At'))
                    ->dateTime('d/m/Y G:i')
                    ->toggleable(isToggledHiddenByDefault: true),

                Tables\Columns\TextColumn::make('updated_at')
                    ->label(__('Updated At'))
                    ->dateTime('d/m/Y G:i')
                    ->toggleable(isToggledHiddenByDefault: true),

                Tables\Columns\TextColumn::make('deleted_at')
                    ->label(__('Deleted At'))
                    ->dateTime('d/m/Y G:i')
                    ->toggleable(isToggledHiddenByDefault: true),

            ])
            ->filters([
                Tables\Filters\TrashedFilter::make(),
            ])
            ->bulkActions([
                Tables\Actions\DeleteBulkAction::make(),
                Tables\Actions\ForceDeleteBulkAction::make(),
                Tables\Actions\RestoreBulkAction::make(),
            ]);
    }

    public static function table(Table $table): Table
    {
        return $table
            ->columns([

                Tables\Columns\TextColumn::make('asset_name')
                    ->label(__('Asset'))
                    ->searchable()
                    ->sortable(),

                Tables\Columns\TextColumn::make('location_name')
                    ->label(__('Location'))
                    ->searchable()
                    ->sortable(),

                Tables\Columns\TextColumn::make('Asset.unitMeasure.name')
                    ->label(__('Unit Measure'))
                    ->searchable()
                    ->sortable()
                    ->toggleable(isToggledHiddenByDefault: true),

                Tables\Columns\TextColumn::make('quantity')
                    ->label(__('Quantity'))
                    ->searchable()
                    ->sortable()
                    ,//->suffix(fn ($record) => ' '.$record->Asset->unitMeasure->name),

                Tables\Columns\TextColumn::make('created_at')
                    ->label(__('Created At'))
                    ->dateTime('d/m/Y G:i')
                    ->toggleable(isToggledHiddenByDefault: true),

                Tables\Columns\TextColumn::make('updated_at')
                    ->label(__('Updated At'))
                    ->dateTime('d/m/Y G:i')
                    ->toggleable(isToggledHiddenByDefault: true),

                Tables\Columns\TextColumn::make('deleted_at')
                    ->label(__('Deleted At'))
                    ->dateTime('d/m/Y G:i')
                    ->toggleable(isToggledHiddenByDefault: true),

            ])
            ->filters([
                Tables\Filters\TrashedFilter::make(),
            ])
            ->bulkActions([
                Tables\Actions\DeleteBulkAction::make(),
                Tables\Actions\ForceDeleteBulkAction::make(),
                Tables\Actions\RestoreBulkAction::make(),
            ]);
    }

cheesegrits•15mo ago

This just seems like the wrong approach. Feels like you should be using Eloquent's built in aggregate functionality. I'm also suspicious of using a random() as the 'id'. And feels like you'll end up with multiple rows for each Inventory.

gon.exeOP•15mo ago

Thanks Hugh, I changed the query for the following and it works (until the moment):

protected function getTableQuery(): Builder
    {

        //  Return all inventories records but group by asset_id and sum the quantity
        return Inventory::selectRaw('
            random() as id,
            asset_id,
            location_id,
            sum(quantity) as quantity
        ')
        ->addSelect('assets.name as asset_name')
        ->addSelect('locations.name as location_name')
        ->groupBy('asset_id', 'location_id', 'asset_name', 'location_name')
        ->leftJoin('assets', 'assets.id', '=', 'inventories.asset_id')
        ->leftJoin('locations', 'locations.id', '=', 'inventories.location_id');
        //

    }

protected function getTableQuery(): Builder
    {

        //  Return all inventories records but group by asset_id and sum the quantity
        return Inventory::selectRaw('
            random() as id,
            asset_id,
            location_id,
            sum(quantity) as quantity
        ')
        ->addSelect('assets.name as asset_name')
        ->addSelect('locations.name as location_name')
        ->groupBy('asset_id', 'location_id', 'asset_name', 'location_name')
        ->leftJoin('assets', 'assets.id', '=', 'inventories.asset_id')
        ->leftJoin('locations', 'locations.id', '=', 'inventories.location_id');
        //

    }

cheesegrits•15mo ago

Is there a specific reason you can use the built in aggregate functions for summing relations?

gon.exeOP•15mo ago

Hello @cheesegrits , no. How do you suggest I make the query? Thank you!

cheesegrits•15mo ago

Well, I don't know exactly what you are trying to achieve, but Filament supports Laravel's relationship aggregation queries. So in this example, I have a relationship called flightLegs, and I want to sum the total of the hobbs_flight_time field from that related table on each row of my main table.

            TextColumn::make('flight_legs_sum_hobbs_flight_time')
                ->label('Flight Hours')
                ->sum('flightLegs', 'hobbs_flight_time')
                ->numeric(),

            TextColumn::make('flight_legs_sum_hobbs_flight_time')
                ->label('Flight Hours')
                ->sum('flightLegs', 'hobbs_flight_time')
                ->numeric(),

As per the Filament docs, this just requires naming your aggregate field as <table_name>_sum_<field_name> (as per Laravel documentation), and then adding the sum(<relationship name>, <field_name>) method for FIlament. No additional query modifications required. I just don't know if this provides what you need to do, as I can't really tell from your code exactly what it is you are trying to do. https://filamentphp.com/docs/3.x/tables/columns/relationships#aggregating-relationships

Column relationships - Table Builder - Filament

Gaming

Programming

Custom getTableQuery() shows bad information after create record.