FilamentF
Filament•3y ago
Laurens

Searching by case insensitive json columns in global search

Hello,

A colleague of me is having an issue where he isn't able to use the global search to find records which have data stored in JSON columns when the value contains capital letters. For example, the search query Serving or serving has no results, while erving would return the desired record.

I've tried to look into this by overwriting the global search query. Here I've managed to fetch the term being searched (not sure if there's a better way :D), but passing the $term value of putting hardcoded 'serving' doesn't result in the record being found either:

protected static function getGlobalSearchEloquentQuery(): Builder
    {
        $updates = collect(request()?->get('updates') ?: []);

        $term = $updates
            ->filter(function (array $update) {
                $type = data_get($update, 'type');
                $payload = data_get($update, 'payload.name');

                if ($type !== 'syncInput') {
                    return false;
                }

                return $payload === 'search';
            })
            ->value('payload.value');

        if (! $term) {
            return parent::getGlobalSearchEloquentQuery();
        }

        $term = strtolower($term);

        return parent::getGlobalSearchEloquentQuery()->orWhereRaw("LOWER(content) LIKE '%serving%'");
    }


The string we're searching in the example contains - Serving assets from S3 inside the JSON column.

What could be a solution here?

Thanks in advance!
Was this page helpful?