Use MySQL query results as a table in Filament

Hi All

I have this MySQL query in a PHP page: $sql = "SELECT jobindex,Silo0, NewWeight, OrderNumber, R_Type, R_Manufacturer, RawMatWeightActual, DensityMean, Comment, DateTimeStop

FROM silo_update WHERE Silo0 NOT LIKE '-----' AND RawMatWeightActual<> 0 AND jobindex IN ( SELECT MAX(jobindex) FROM silo_update GROUP BY Silo0) ORDER BY LENGTH(Silo0), Silo0";

I used an online MySQL to Eloquent tool and converted it to:

DB::table('silo_update') ->select('jobindex', 'Silo0', 'siloSwapped', 'OrderNumber', 'R_Type', 'R_Manufacturer', 'RawMatWeightActual', 'DensityMean', 'Comment', 'DateTimeStop') ->where('RawMatWeightActual', '>', 0) ->whereIn('jobindex', function ($query) { $query->select(DB::raw('MAX(jobindex)')) ->from('silo_update') ->groupBy('Silo0'); }) ->orderByRaw('LENGTH(Silo0), Silo0') ->limit(40) ->get();

If I dd the query I get what I need but I've got no clue how to change the Resource file use the query results instead of just dumping the table as is.

Any help will be high appreciated as I've spent most of the week trying to figure it out.

Thanks

LaryAI

1 year ago

Level 58

To use the results of your custom MySQL query in a Filament Resource, you need to override the getTableQuery method in your Resource class. This method allows you to customize the query that Filament uses to fetch data for the table.

Here's how you can do it:

Create or Update Your Filament Resource: If you haven't already created a Filament Resource for your silo_update table, you can generate one using the Filament command. If you already have one, you can skip this step.
```
php artisan make:filament-resource SiloUpdate
```

Override the getTableQuery Method: In your SiloUpdateResource.php file, override the getTableQuery method to use your custom query.

namespace App\Filament\Resources;

use Filament\Resources\Resource;
use Filament\Resources\Table;
use Filament\Tables;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class SiloUpdateResource extends Resource
{
    // Other methods and properties...

    public static function getTableQuery(): Builder
    {
        return DB::table('silo_update')
            ->select('jobindex', 'Silo0', 'NewWeight', 'OrderNumber', 'R_Type', 'R_Manufacturer', 'RawMatWeightActual', 'DensityMean', 'Comment', 'DateTimeStop')
            ->where('RawMatWeightActual', '>', 0)
            ->whereIn('jobindex', function ($query) {
                $query->select(DB::raw('MAX(jobindex)'))
                    ->from('silo_update')
                    ->groupBy('Silo0');
            })
            ->orderByRaw('LENGTH(Silo0), Silo0');
    }

    public static function table(Table $table): Table
    {
        return $table
            ->columns([
                Tables\Columns\TextColumn::make('jobindex'),
                Tables\Columns\TextColumn::make('Silo0'),
                Tables\Columns\TextColumn::make('NewWeight'),
                Tables\Columns\TextColumn::make('OrderNumber'),
                Tables\Columns\TextColumn::make('R_Type'),
                Tables\Columns\TextColumn::make('R_Manufacturer'),
                Tables\Columns\TextColumn::make('RawMatWeightActual'),
                Tables\Columns\TextColumn::make('DensityMean'),
                Tables\Columns\TextColumn::make('Comment'),
                Tables\Columns\TextColumn::make('DateTimeStop'),
            ])
            ->filters([
                // Add any filters you need here
            ])
            ->actions([
                Tables\Actions\EditAction::make(),
            ])
            ->bulkActions([
                Tables\Actions\DeleteBulkAction::make(),
            ]);
    }
}

Register the Resource: Ensure that your resource is registered in the FilamentServiceProvider if it isn't already.

use App\Filament\Resources\SiloUpdateResource;

public function register()
{
    Filament::registerResources([
        SiloUpdateResource::class,
    ]);
}

By overriding the getTableQuery method, you can customize the query that Filament uses to fetch data for the table. This allows you to use the results of your custom MySQL query in the Filament table.

Make sure to adjust the columns and other configurations in the table method to match your requirements.