F
Filamentβ€’17mo 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%'");
}
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!
2 Replies
Dan Harrin
Dan Harrinβ€’17mo ago
You should do this in getGlobalSearchResults()
Laurens
LaurensOPβ€’17mo ago
Thanks I'll check tonight πŸ™‚ Thanks that did it! Managed to solve it with the following now.. Forwarded it to my colleague. πŸ™‚ (replaced the actual model name with the base one here)
public static function getGlobalSearchResults(string $searchQuery): Collection
{
$searchQuery = strtolower($searchQuery);

$customResults = Model::query()
->whereRaw("LOWER(content) LIKE ?", ["%$searchQuery%"])
->get();

return parent::getGlobalSearchResults($searchQuery)
->concat($customResults)
->unique()
->values();
}
public static function getGlobalSearchResults(string $searchQuery): Collection
{
$searchQuery = strtolower($searchQuery);

$customResults = Model::query()
->whereRaw("LOWER(content) LIKE ?", ["%$searchQuery%"])
->get();

return parent::getGlobalSearchResults($searchQuery)
->concat($customResults)
->unique()
->values();
}
Want results from more Discord servers?
Add your server