[Bug] Fetch operation is case-sensitive in multilanguage CRUDs

[tabacitu]

Bug report

What I did

Made some of my models multi-language in an admin panel.

What I expected to happen

When I have relationship fields that need search... for it to work.

What happened

Relationship fields that point to translatable models... do not work as expected. They do not "search properly", in that the search has become case-sensitive.

CleanShot.2025-08-15.at.12.31.35.mp4

What I've already tried to fix it

Nothing yet - we plan to create our own custom FetchOperation in that project. But this really should be a feature that Backpack offers out-of-the-box, because if we support translatable models... we should also support them being searchable as expected, right?

[tabacitu]

Here's what I ended up doing in my project... that fixes both this issue and #5848 ... without any Backpack-specific overrides.

Bear with me here... because it's a bit crazy. But... I made the search case-insensitive on the Eloquent level 👀

I have this in App\Traits\CaseInsensitiveSearch.php:

<?php

namespace App\Traits;

use App\Database\CaseInsensitiveBuilder;

/**
 * Trait to enable case-insensitive search for translatable JSON columns.
 *
 * Add this trait to any model that uses spatie/laravel-translatable to make
 * LIKE searches case-insensitive. Fixes the issue where searching "bucharest"
 * wouldn't find "Bucharest" in Backpack CRUD interfaces and other searches.
 */
trait HasCaseInsensitiveSearch
{
    /**
     * Create a new Eloquent query builder for the model that supports
     * case-insensitive searches for translatable JSON columns.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @return \App\Database\CaseInsensitiveBuilder
     */
    public function newEloquentBuilder($query)
    {
        return new CaseInsensitiveBuilder($query);
    }

}

and this in App\Database\CaseInsensitiveBuilder.php:

<?php

namespace App\Database;

use App\Services\LocalizationService;
use Illuminate\Database\Eloquent\Builder;

/**
 * Custom Eloquent Builder that makes LIKE searches case-insensitive for translatable JSON columns.
 *
 * Problem: spatie/laravel-translatable stores translations as JSON (e.g., {"en": "Bucharest", "ro": "București"}).
 * By default, MySQL JSON searches are case-sensitive, so searching "bucharest" wouldn't find "Bucharest".
 * This was particularly problematic in Backpack CRUD interfaces where relationship fields (like City in Venue)
 * became unusable due to case-sensitive search behavior.
 *
 * Solution: Override where() and orWhere() methods to automatically detect translatable JSON columns
 * and apply case-insensitive LOWER() functions to both the JSON values and search terms.
 *
 * Usage: Add HasCaseInsensitiveSearch trait to any translatable model.
 *
 * Safety: Only affects LIKE operations on translatable columns. Exact matches remain case-sensitive.
 */
class CaseInsensitiveBuilder extends Builder
{
    public function where($column, $operator = null, $value = null, $boolean = 'and')
    {
        // Handle Laravel's shorthand where($column, $value) syntax
        if (func_num_args() === 2 && is_null($operator)) {
            $value = $operator;
            $operator = '=';
        } elseif (func_num_args() === 2) {
            $value = $operator;
            $operator = '=';
        }

        // Check if this should be a case-insensitive translatable search
        if ($this->shouldUseTranslatableSearch($column, $operator, $value)) {
            return $this->whereTranslatableJson($column, $value, $boolean);
        }

        return parent::where($column, $operator, $value, $boolean);
    }

    public function orWhere($column, $operator = null, $value = null)
    {
        // Handle Laravel's shorthand orWhere($column, $value) syntax
        if (func_num_args() === 2 && is_null($operator)) {
            $value = $operator;
            $operator = '=';
        } elseif (func_num_args() === 2) {
            $value = $operator;
            $operator = '=';
        }

        // Check if this should be a case-insensitive translatable search
        if ($this->shouldUseTranslatableSearch($column, $operator, $value)) {
            return $this->whereTranslatableJson($column, $value, 'or');
        }

        return parent::orWhere($column, $operator, $value);
    }


    /**
     * Determine if we should use case-insensitive translatable search
     */
    protected function shouldUseTranslatableSearch($column, $operator, $value): bool
    {
        // Only for LIKE operations (case-insensitive check)
        if (strtoupper($operator) !== 'LIKE') {
            return false;
        }

        // Only for string values
        if (!is_string($value)) {
            return false;
        }

        // Only for models with translatable attributes
        $model = $this->getModel();
        if (!method_exists($model, 'isTranslatableAttribute')) {
            return false;
        }

        // Only for columns that are actually translatable
        // Strip table prefix if present (e.g., "cities.name" -> "name")
        $columnName = str_contains($column, '.') ? substr($column, strrpos($column, '.') + 1) : $column;
        return $model->isTranslatableAttribute($columnName);
    }

    /**
     * Apply case-insensitive search across all locales for a translatable JSON column
     */
    protected function whereTranslatableJson($column, $value, $boolean = 'and')
    {
        // Use the column as-is if it already has table prefix, otherwise add it
        $fullColumnName = str_contains($column, '.') ? $column : $this->getModel()->getTable() . '.' . $column;

        return $this->where(function ($query) use ($fullColumnName, $value) {
            $locales = array_keys(LocalizationService::getLaravelLocalizationLocales());

            foreach ($locales as $locale) {
                $query->orWhereRaw(
                    "LOWER(JSON_UNQUOTE(JSON_EXTRACT({$fullColumnName}, ?))) LIKE LOWER(?)",
                    ['$."' . $locale . '"', $value]
                );
            }
        }, null, null, $boolean);
    }
}

And... it just works. Everything works out-of-the box - in ListOperation search, in select2 search... it works.

Now... it's a bit intrusive... to fix this at an Eloquent level... but I would argue that... the default search SHOULD be case-insensitive. It's the USER-FRIENDLY way, and the DEFAULT way in normal search.

Now in terms of what to do in Backpack itself... I dunno... Maybe we just provide these 2 files? 🤷🏻‍♂️ Maybe we change our current trait to have this out-of-the-box... again... i really don't know 🤷🏻‍♂️

[pxpm]

This is now fixed in the latest release of the PRO package.

Thanks @tabacitu for the heads up 🙏