How To Use Filament Table Builder with Laravel Database Query Builder Instead of Eloquent Builder

[aimonext]

Occasionally, in tables, we employ multiple table joins to fetch and display data. In certain scenarios, using Query Builder instead of Eloquent Builder may be necessary. Are there any ways to implement this approach?

[leandrocfe]

Only the Eloquent, I guess.
You could create a database view and use the db view as a table

class YourView extends Eloquent {

    protected $table = 'your_db_view';
}

[mtbossa]

Where/when would you generate/update this view? I have a schedules table and a schedule_exceptions table, and I need to create a single table to show content from both these tables (a somewhat complex SQL). How would you approach this?

Also, I'd need a view for the search date filter, something like this: $table = "appointments_view_<date-filter-value>", e.g.: $table = "appointments_view_2024-05-05"

[monkeyphysics]

Where/when would you generate/update this view? I have a schedules table and a schedule_exceptions table, and I need to create a single table to show content from both these tables (a somewhat complex SQL). How would you approach this?

Also, I'd need a view for the search date filter, something like this: $table = "appointments_view_<date-filter-value>", e.g.: $table = "appointments_view_2024-05-05"

I came across this post, and it put me on the right track. You simply create a migration to create the database view:

public function up(): void
{
    DB::statement("
        CREATE OR REPLACE VIEW record_index_views AS
        SELECT `records`.*,
               ...
        FROM `records`
        INNER JOIN `artists` ...
        INNER JOIN `genres` ...
        INNER JOIN ...
    ");
}
 
public function down(): void
{
    DB::statement('DROP VIEW IF EXISTS record_index_views;');
}

Something to consider is your model should be named RecordIndexView (singular!) and might need some extra configuration, like:

protected $table = 'record_index_views';
protected $primaryKey = null;
public $timestamps = false;
public $incrementing = false;

This all works fine. The only issue I run into is that the Filament table based on my view has only simple pagination (next/previous), because it seemingly doesn't know how many pages there are. Any help on this would be appreciated.