Filament•13mo ago

Optimising Search Functionality in FilamentPHP Player Table: Seeking Feedback and Suggestions

Hi everyone! I've been working on optimising my PlayerResource table in FilamentPHP and wanted to share my approach, especially regarding the searchable method. Here's what I've implemented:

public static function table(Table $table): Table
{
    return $table
        ->columns([
            ViewColumn::make('username')
                ->label('Player')
                ->searchable(query: function (Builder $query, string $search): Builder {
                    $countryCodes = CountryEmojiHelper::getCountryCodesFromName($search);
                    return $query->where(function (Builder $subQuery) use ($search, $countryCodes) {
                        $subQuery->where('username', 'like', "%{$search}%")
                                 ->orWhere('minecraft_uuid', 'like', "%{$search}%")
                                 ->orWhereIn('country_code', $countryCodes);
                    });
                })
                ->sortable()
                ->view('admin.players.columns.username'),
            TextColumn::make('last_login_at')
                ->label('Last Login')
                ->sortable(),
        ]);
}

public static function table(Table $table): Table
{
    return $table
        ->columns([
            ViewColumn::make('username')
                ->label('Player')
                ->searchable(query: function (Builder $query, string $search): Builder {
                    $countryCodes = CountryEmojiHelper::getCountryCodesFromName($search);
                    return $query->where(function (Builder $subQuery) use ($search, $countryCodes) {
                        $subQuery->where('username', 'like', "%{$search}%")
                                 ->orWhere('minecraft_uuid', 'like', "%{$search}%")
                                 ->orWhereIn('country_code', $countryCodes);
                    });
                })
                ->sortable()
                ->view('admin.players.columns.username'),
            TextColumn::make('last_login_at')
                ->label('Last Login')
                ->sortable(),
        ]);
}

In this setup, I've focused on making the username column searchable, including searching by Minecraft UUID and country codes. The username.blade.php view also displays the player's country flag and their Minecraft UUID, which is why I chose not to include separate columns for them in the table. I'm keen to know if this is the most efficient approach or if there are better ways to handle this. Specifically, I'm curious about the use of the searchable method and its performance implications. Any feedback or suggestions would be greatly appreciated!

2 Replies

Lara Zeus•13mo ago

cant say for sure, a lot to take into consideration but use a debugbar or simillar tool to get the queries applied after the search and the time it took also, make sure to add indexes for these columns

ChesterS•13mo ago

I'm in no way an expert, so take the following with a grain of salt like queries are hard to optimise (especially like '%...%'). In most cases the engine will need to do a full table scan since indexes are not helpful in those cases. Even full-text indexes are not really helpful since they work on full-words and not partial matches. I had a similar issue recently and did some research. The consensus was that if you need this type of fuzzy search, it's better to use services/structures optimised for that (eg elasticsearch etc) Not sure if MySQL (or w/e you're using) has something similar - you'll have to do a deeper dive to figure it out.

Gaming

Programming

Optimising Search Functionality in FilamentPHP Player Table: Seeking Feedback and Suggestions