I have a curious conundrum about json fields and eloquent's whereJsonContains() method.
An eloquent model with a json field, so in this example its migration has something like
public function up(): void
{
Schema::create('blogs', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->json('categories');
$table->timestamps();
});
Blog::create([
'name' => 'Alpha',
'categories' => ['A'],
]);
Blog::create([
'name' => 'Epsilon',
'categories' => ['È'],
]);
}
and the model is something like
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Casts\AsArrayObject;
use Illuminate\Database\Eloquent\Model;
class Blog extends Model
{
protected $fillable = [
'name',
'categories',
];
protected $casts = [
'categories' => AsArrayObject::class,
];
}
After migrating, if I try this query in a controller it will work
dd(Blog::whereJsonContains('categories', 'A')->get());
but this one won't
dd(Blog::whereJsonContains('categories', 'È')->get());
If you look in the database you'll see a unicode instead of the È. Obviously I tried json_encode, but it doesn't work.
What indeed work is writing some long string like this one, but I don't know why it works.
dd(Blog::whereJsonContains('categories', DB::raw('CONCAT(\'"\',' . str(json_encode('È'))->replace('\\', '\\\\') . ',\'"\')'))->get());
Does anyone has an idea of what's going on?
PS: Laravel 10.13.5 , PHP 8.1.4 , MariaDB 10.4.24
json_encode() by default escapes unicode characters. You can try overwriting the json_encode part in AsArrayObject to encode your data with the following flag: json_encode($yourData, JSON_UNESCAPED_UNICODE).
Depending on your use case of "categories", wouldn't it be more efficient and future proof to create a table categories and a pivot table blog_categories where you link a blog with multiple queries? That way you can easily search on categories or rename a category
Thank you for directing me to (what I think is) a good solution.
Looking around in Laravel files I found that AsArrayObject class uses a Json class to encode jsons. That class also has a static method to set a custom encoder, otherwise the Json class uses a default json_encode() method.
So I tried this dd in my controller:
use Illuminate\Database\Eloquent\Casts\Json;
// ...
dd(
Json::encode('È'), // ""\u00c8""
Json::encodeUsing(fn ($value) => json_encode($value, JSON_UNESCAPED_UNICODE)), // it should go in AppServiceProvider boot() method
Json::encode('È'), // ""È""
);
And so, after putting the encodeUsing() in the AppServiceProvider boot() method and migrating the database again, now this work:
dd(Blog::whereJsonContains('categories', 'È')->get()); // this now works!
I don't know if I'm missing something important, but I hope this little trick will help others when encoding special characters globally.
PS about the pivot table you mentioned: you are definitely right and categories should be a model on their own, but having very few categories I wanted to try a json approach to experiment a little.
Make sure your database encoding allowing theses characters. Mostly it should be utf8mb4_unicode_ci
Please or to participate in this conversation.