Use where like for relative search in laravel-CodePudding

I am doing data search function in php . I use mongoDB. I have a table user:

id        name         status
1         tèo           1
2         tý            2

In UserRepository.php :

<?php


namespace App\Repositories\Backend\MongoDB;

use App\Models\User;
use App\Repositories\MongoDBBaseRepository;

class UserRepository extends MongoDBBaseRepository
{
    public function model()
    {
        return MoviePassContent::class;
    }
    public function search($keyword)
    {
        $search = $this->model->where('name', 'like', '%'.$keyword.'%')->get()->toArray();
        // I tried more of these ways, but it still doesn't work
        // $search = $this->model->where('name', 'like', $keyword.'%')->get()->toArray();
        // $search = $this->model->where('name', 'like', '%'.$keyword)->get()->toArray();
    }
}

If the $keyword I enter is : tèo, the result is correct. But when I enter $keyword as teo or t it returns null. Where did I go wrong. Please give me your opinion. Thank you.

CodePudding user response：

It's not related to Laravel or PHP. It all depends on your database. Try to utilize a different collation charset on your database.

Example below changes your collation and charset to utf32. Careful, make sure to backup your database to prevent any data loss! This action may cause partial or full data loss:

ALTER DATABASE your_db_name
    CHARACTER SET utf32]
    COLLATE utf32_general_ci

You may tweak it to find the best fit for you, it depends on what kind of data you're inserting into the db.

Another safer alternative is to mitigate it on application layer. Define a scope which will help with special character conversions.

As for the: "searching by 't' but not appearing" issue, it seems like DBMS config problem. Even though it usually applies for FULLTEXT search, there might be some setting about min-length regarding LIKE queries.

CodePudding user response：

Assuming you are using jenssegers/laravel-mongodb then you can achieve this with ->options to your query:

        $search = $this->model
             ->where('name', 'like', '%'.$keyword.'%')
             ->options([ 
                 'collation' => [
                    'locale' => 'en',
                    'strength' => 1  
                  ]
            ])->get()->toArray();

Check https://www.mongodb.com/docs/v5.3/reference/collation/ for more details on the collation option