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

Serving

serving

serving

has no results, while

erving

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%'");
    }

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

- Serving assets from S3

inside the JSON column.

What could be a solution here?

Thanks in advance!

Searching by case insensitive json columns in global search

Serving

Serving

serving

serving

has no results, while

erving

erving

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%'");
    }

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

- Serving assets from S3

inside the JSON column.

What could be a solution here?

Thanks in advance!

Searching by case insensitive json columns in global search

Similar Threads

Searching by case insensitive json columns in global search

Similar Threads

Similar Threads

Similar Threads