search

statamic / eloquent-driver

Provides support for storing your Statamic data in a database, rather than flat files.
https://statamic.dev/tips/storing-content-in-a-database
MIT License
104 stars 74 forks source link

Tag Conditions Case Sensitivity #104

Closed Z3d0X closed 1 year ago

Z3d0X commented 1 year ago

When using Tag Conditions (such as contains), along with this eloquent driver it does a case sensitive search. With the default file driver it does a case in-sensitive

Z3d0X commented 1 year ago

It seems to related to querying json columns Ref: https://laracasts.com/discuss/channels/laravel/how-can-i-solve-case-sensitive-where-like-statement-on-json-data-in-laravel-eloquent

ryanmitchell commented 1 year ago

My personal feeling is that this is probably going to be something you have to live with when using eloquent driver as the only option would be to make all where() type queries case insensitive, which also wouldn't be ideal.

Z3d0X commented 1 year ago

Yeah i understand it is a limitation of using JSON columns.

@duncanmcclean Suggested this approached on discord, which worked out well. I'm leaving this here for anyone who comes across this issue.

So you could maybe have a migration like this (I'm not 100% on the structure of database entries, never used them, just copied something similar from one of my projects)

Schema::table('entries', function (Blueprint $table) {
   $table->string('title')->virtualAs('json_unquote(json_extract(`data`, "$.title"))')->index()->after('data');
});

It'll create a "virtual column" which will allow you to access & use the title as if it was a real database column.

ref: https://discord.com/channels/489818810157891584/996788399371468861/1057945233326874724