[Bug] Translatable columns become case-sensitive to search

[tabacitu]

Bug report

What I did

I use spatie/laravel-translatable in my app, which Backpack supports. Columns show up FINE out-of-the-box... fields work... BUT... if i try to use the default search in ListOperation... it returns zero results most of the time.

What I expected to happen

Return results. If I have an item with title {'en': 'Smith'} and I search smith I expect to get that, not zero results.

What happened

Our search has one big issue when searching within translatable attributes. It becomes case-sensitive!

This is unexpected... and I think we can fix it pretty easily (though it might mean custom search logic for each DBMS - MySQL, PostgreSQL, SQLite etc - bleah). Alternatively... if we want to go the non-breaking route... we can maybe fix it using some built-in helpers... that will provide the search logic? And we tell people they can use those if they need them? Hacky but still helpful...

What I've already tried to fix it

In my project, I've fixed it using:

    // custom search logic in a separate file
    CRUD::column('title')->searchLogic(TranslatableSearch::auto());

    // also custom search logic for when needing to search within multiple columns
    CRUD::column('name')->searchLogic(TranslatableSearch::across(['hash', 'slug', 'name']));

with the file itself being:

<?php

namespace App\Helpers;

use Illuminate\Database\Eloquent\Builder;

class TranslatableSearch
{
    /**
     * Get a reusable search logic closure that automatically handles translatable fields.
     * Automatically detects if a field is translatable and applies appropriate search logic.
     *
     * @param mixed $model Optional model instance for context
     * @return \Closure
     */
    public static function auto($model = null)
    {
        return function (Builder $query, array $column, string $searchTerm) use ($model) {
            // Auto-detect model if not provided
            $modelInstance = $model ?? $query->getModel();

            // Check if the model has translatable capabilities and if this field is translatable
            if (method_exists($modelInstance, 'isTranslatableAttribute') &&
                $modelInstance->isTranslatableAttribute($column['name'])) {

                static::searchTranslatableField($query, $column, $searchTerm);
            } else {
                static::searchRegularField($query, $column, $searchTerm);
            }
        };
    }

    /**
     * Create a search logic closure for multiple fields at once.
     * Useful when you want to search across multiple columns with one search term.
     *
     * @param array $fieldNames Array of field names to search
     * @param mixed $model Optional model instance
     * @return \Closure
     */
    public static function across(array $fieldNames, $model = null)
    {
        return function (Builder $query, array $column, string $searchTerm) use ($fieldNames, $model) {
            $modelInstance = $model ?? $query->getModel();

            $query->orWhere(function (Builder $subQuery) use ($fieldNames, $searchTerm, $modelInstance) {
                foreach ($fieldNames as $fieldName) {
                    $fakeColumn = ['name' => $fieldName];

                    if (method_exists($modelInstance, 'isTranslatableAttribute') &&
                        $modelInstance->isTranslatableAttribute($fieldName)) {
                        static::searchTranslatableField($subQuery, $fakeColumn, $searchTerm);
                    } else {
                        static::searchRegularField($subQuery, $fakeColumn, $searchTerm);
                    }
                }
            });
        };
    }

    /**
     * Apply case-insensitive search logic for translatable JSON fields.
     *
     * @param Builder $query
     * @param array $column
     * @param string $searchTerm
     * @return void
     */
    protected static function searchTranslatableField(Builder $query, array $column, string $searchTerm)
    {
        $columnName = $column['name'];
        $tableName = $query->getModel()->getTable();
        $fullColumnName = "{$tableName}.{$columnName}";

        $query->orWhere(function (Builder $subQuery) use ($fullColumnName, $searchTerm) {
            // Get all configured locales
            $locales = static::getConfiguredLocales();

            foreach ($locales as $locale) {
                // Use MySQL JSON functions with case-insensitive search
                $subQuery->orWhereRaw(
                    "LOWER(JSON_UNQUOTE(JSON_EXTRACT({$fullColumnName}, ?))) LIKE LOWER(?)",
                    ['$."' . $locale . '"', '%' . $searchTerm . '%']
                );
            }
        });
    }

    /**
     * Apply case-insensitive search logic for regular text fields.
     *
     * @param Builder $query
     * @param array $column
     * @param string $searchTerm
     * @return void
     */
    protected static function searchRegularField(Builder $query, array $column, string $searchTerm)
    {
        $columnName = $column['name'];
        $tableName = $query->getModel()->getTable();
        $fullColumnName = "{$tableName}.{$columnName}";

        // Use case-insensitive LIKE search for regular fields
        $query->orWhereRaw("LOWER({$fullColumnName}) LIKE LOWER(?)", ['%' . $searchTerm . '%']);
    }

    /**
     * Get the configured locales for the application.
     *
     * @return array
     */
    protected static function getConfiguredLocales(): array
    {
        // Try to get locales from Backpack CRUD config first
        if (config('backpack.crud.locales')) {
            return array_keys(config('backpack.crud.locales'));
        }

        // Fallback to app locales or default
        $fallbackLocales = config('app.available_locales', [config('app.locale', 'en')]);

        return is_array($fallbackLocales) ? $fallbackLocales : [$fallbackLocales];
    }
}