Filtering entries in the stack selector is case sensitive

[robdekort]

As opposed to the flat file driver, filtering entries in the stack selector is case sensitive. It should be case insensitive imo.

1. Add an entries field with a stack selector
2. Open the stack selector
3. Search for entry like 'home'. It won't work. You need to type 'Home'.

I've only noticed it for entries only as this is the only repository I use the Eloquent driver for.

[ryanmitchell]

I think this depends on the collation of your database (case insensitive or not). Do you know what yours is?

[robdekort]

Nope :-). But nice pointer. I'll get back to this tonight. Curious! thanks!

[ryanmitchell]

Hopefully I’m right - from memory it’s that! If your collection has _ci in it it’s case insensitive which would mean I’m wrong!

[robdekort]

Yeah it seems to be utf8mb4_unicode_ci which looks good to me.

[ryanmitchell]

So it seems when the data is in a JSON field MySQL carries out case sensitive comparisons as the data comes back in binary. The solution seem to be either to make column and value lowercase, or to cast the json_unquoted value as CHAR. Both of these feel like we would have to add mysql specific code to the base eloquent query builder.

[robdekort]

I totally believe you 🤯😉

[ryanmitchell]

😂 I think it’s something @jasonvarga would need to make a call on

[ryanmitchell]

Another option would be to create a migration for your app that creates a generated column for title, and then MySQL will defer to using that in any queries:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('entries', function (Blueprint $table) {
            $table->string('title')->storedAs('JSON_UNQUOTE(data->>"$.title")');
        });
    }
};

[Edit:] it still pulls from the json column ... oh well it was a nice thought

[Edit2:] it works when you bind your own query builder... eg

<?php

namespace App;

use Statamic\Eloquent\Entries\EntryQueryBuilder as StatamicEntryQueryBuilder;

class EntryQueryBuilder extends StatamicEntryQueryBuilder
{
    protected function column($column)
    {
        if ($column == 'title') {
            return $column;
        }

        return parent::column($column);
    }
}

Then in your AppServiceProvider::boot()

        $this->app->bind(\Statamic\Contracts\Entries\QueryBuilder::class, function ($app) {
            return new \App\EntryQueryBuilder(
                $app['statamic.eloquent.entries.model']::query()
            );
        });

[robdekort]

Wonderful! Thanks Ryan ❤️.