I have a search mechanism like this, with "client.partner" nested relation:

$query->whereHas("client.partner", function ($query) use ($searchterm, $relation) {
    $query->when($relation === "contains", function ($query) use ($searchterm, $relation) {
        $query->where("partners.name", "LIKE", "%" . strtolower($searchterm) . "%");
    });   
});

The question is, that how "partners.name" in where clause can be lowercased.

I tried to use LOWER() function, as follows, but it did not work:

...
 $query->where(LOWER("partners.name"), "LIKE", "%" . strtolower($searchterm) . "%");
...

Any idea?

>Solution :

You can use the DB::raw() method to lowercase the value of the "partners.name" column in the where clause. Here is an example:

$query->whereHas("client.partner", function ($query) use ($searchterm, $relation) {
    $query->when($relation === "contains", function ($query) use ($searchterm, $relation) {
        $query->where(DB::raw("LOWER(partners.name)"), "LIKE", "%" . strtolower($searchterm) . "%");
    });   
});

The DB::raw() method allows you to write raw SQL expressions within the Eloquent query. In this case, we use the LOWER() SQL function to convert the value of "partners.name" to lowercase before comparing it with the search term.

Note: Make sure you import the DB facade at the top of your file:

use Illuminate\Support\Facades\DB;