Filament•3mo ago

How to Customize Filament Table Queries in Laravel: Help Needed!

Hello everyone, I hope this isn't a repeated or silly question. I've tried various searches but couldn't find the result I was hoping for. I have a table connected to a Laravel model, but the data I want to display in the Filament table is completely customized compared to the classic ->get()->all() that I believe Filament uses by default. To clarify, in my GridJobImportResource resource, I'd like the default query to be this: --- Query in the comment below --- (It's still static in some parts, but it works, so for testing purposes, it's fine for now). I've tried everything to override the query, using methods like getEloquentQuery, getTableQuery, or even query directly on the $table variable, but with no success. Is there an easy way to achieve this, or am I forced to use something like Sushi? Thanks to everyone for your help! Cheers!

3 Replies

SirAlyonOP•3mo ago

Here the query:

SELECT tab.ijob, CONCAT(COUNT(1), ' (', SUM(tab0.irighe),')') AS '2024-09-18', CONCAT(COUNT(1), ' (', SUM(tab1.irighe),')') AS '2024-09-17'
FROM jobs_import_grid AS tab

LEFT JOIN (SELECT
    ijob,
    irighe,
    tuts
FROM
    jobs_import_grid
WHERE
    cstato = 'A'
    AND tuts >= '2024-09-18'
GROUP BY ijob) AS tab0
ON tab.ijob = tab0.ijob AND tab.tuts = tab0.tuts

LEFT JOIN (SELECT
    ijob,
    irighe,
    tuts
FROM
    jobs_import_grid
WHERE
    cstato = 'A'
    AND tuts >= '2024-09-17'
GROUP BY ijob) AS tab1
ON tab.ijob = tab1.ijob AND tab.tuts = tab1.tuts

GROUP BY tab.ijob

SELECT tab.ijob, CONCAT(COUNT(1), ' (', SUM(tab0.irighe),')') AS '2024-09-18', CONCAT(COUNT(1), ' (', SUM(tab1.irighe),')') AS '2024-09-17'
FROM jobs_import_grid AS tab

LEFT JOIN (SELECT
    ijob,
    irighe,
    tuts
FROM
    jobs_import_grid
WHERE
    cstato = 'A'
    AND tuts >= '2024-09-18'
GROUP BY ijob) AS tab0
ON tab.ijob = tab0.ijob AND tab.tuts = tab0.tuts

LEFT JOIN (SELECT
    ijob,
    irighe,
    tuts
FROM
    jobs_import_grid
WHERE
    cstato = 'A'
    AND tuts >= '2024-09-17'
GROUP BY ijob) AS tab1
ON tab.ijob = tab1.ijob AND tab.tuts = tab1.tuts

GROUP BY tab.ijob

I'm trying something like:

public static function getEloquentQuery(): Builder
    {
        $today = Carbon::now()->format('Y-m-d');
        $yesterday = Carbon::now()->subDay()->format('Y-m-d');

        // Crea sottoquery per il giorno attuale (2024-09-18)
        $subqueryTab0 = GridJobImport::select('ijob')
            ->selectRaw('SUM(irighe) as irighe')
            ->where('cstato', 'A')
            ->where('tuts', '>=', $today)
            ->groupBy('ijob');

        // Crea sottoquery per il giorno precedente (2024-09-17)
        $subqueryTab1 = GridJobImport::select('ijob')
            ->selectRaw('SUM(irighe) as irighe')
            ->where('cstato', 'A')
            ->where('tuts', '>=', $yesterday)
            ->groupBy('ijob');

        // Eloquent query con leftJoinSub
        return GridJobImport::query()
            ->leftJoinSub($subqueryTab0, 'tab0', function ($join) {
                $join->on('jobs_import_grid.ijob', '=', 'tab0.ijob');
            })
            ->leftJoinSub($subqueryTab1, 'tab1', function ($join) {
                $join->on('jobs_import_grid.ijob', '=', 'tab1.ijob');
            })
            ->select('jobs_import_grid.ijob')
            ->selectRaw("CONCAT(COUNT(jobs_import_grid.ijob), ' (', COALESCE(SUM(tab0.irighe), 0), ')') AS '2024-09-18'")
            ->selectRaw("CONCAT(COUNT(jobs_import_grid.ijob), ' (', COALESCE(SUM(tab1.irighe), 0), ')') AS '2024-09-17'")
            ->groupBy('jobs_import_grid.ijob');
    }

public static function getEloquentQuery(): Builder
    {
        $today = Carbon::now()->format('Y-m-d');
        $yesterday = Carbon::now()->subDay()->format('Y-m-d');

        // Crea sottoquery per il giorno attuale (2024-09-18)
        $subqueryTab0 = GridJobImport::select('ijob')
            ->selectRaw('SUM(irighe) as irighe')
            ->where('cstato', 'A')
            ->where('tuts', '>=', $today)
            ->groupBy('ijob');

        // Crea sottoquery per il giorno precedente (2024-09-17)
        $subqueryTab1 = GridJobImport::select('ijob')
            ->selectRaw('SUM(irighe) as irighe')
            ->where('cstato', 'A')
            ->where('tuts', '>=', $yesterday)
            ->groupBy('ijob');

        // Eloquent query con leftJoinSub
        return GridJobImport::query()
            ->leftJoinSub($subqueryTab0, 'tab0', function ($join) {
                $join->on('jobs_import_grid.ijob', '=', 'tab0.ijob');
            })
            ->leftJoinSub($subqueryTab1, 'tab1', function ($join) {
                $join->on('jobs_import_grid.ijob', '=', 'tab1.ijob');
            })
            ->select('jobs_import_grid.ijob')
            ->selectRaw("CONCAT(COUNT(jobs_import_grid.ijob), ' (', COALESCE(SUM(tab0.irighe), 0), ')') AS '2024-09-18'")
            ->selectRaw("CONCAT(COUNT(jobs_import_grid.ijob), ' (', COALESCE(SUM(tab1.irighe), 0), ')') AS '2024-09-17'")
            ->groupBy('jobs_import_grid.ijob');
    }

but it trigger: Filament\Resources\Pages\ListRecords::getTableRecordKey(): Return value must be of type string, null returned.

nathan•2mo ago

Hey dude, Here is what you need to do. First, Filament is expecting a record key associated with each row. That's not the case in a groupBy statement. However, you can get around it by defining a key using the method getTableRecordKey in the resource you are adding the table to. For instance, I have a Table widget and a query using a groupBy statement. I'm using whatever I'm grouping by as my key as I can guarantee it will be unique for each row. Here is an example that you can modify for your own use:

Matthew•2mo ago

Here is the bit you are missing: https://filamentphp.com/docs/3.x/panels/resources/listing-records#customizing-the-table-eloquent-query

Listing records - Panel Builder - Filament

Gaming

Programming

How to Customize Filament Table Queries in Laravel: Help Needed!