WHERE LIKE searching in Eloquent with and without spacing

We’ve made a search system on our website, with a brands table being made. There are some brands that have spaces in their names and some that don’t.

We have for example

“Turtle Wax”

What I’d like to do, is when a user searches for say Turtlewax my search looks for both versions…

Turtle Wax
TurtleWax

Is this even possible?

My current code is as follows…

$products = Product::select('product.*')
                    ->join('brand', 'product.brand_id', '=', 'brand.id')
                    ->join('child_product', 'product.id', '=', 'child_product.product_id')
                    ->leftJoin('tax_rates', 'tax_rates.id', '=', 'child_product.tax_rate_id')
                    ->with('brand', 'categories', 'childProducts', 'childProducts.attributeValues', 'images')
                    ->where('product.active', true)
                    ->where('brand.active', true)
                    ->where(function ($query) use ($search_term)
                    {
                        $query->whereLike('product.name', $search_term)
                              ->orWhereLike('product.description', $search_term)
                              ->orWhere('brand.slug', str_slug($search_term, ''))
                              ->orWhereLike('brand.slug', str_slug($search_term, '-'))
                              ->orWhereLike('brand.name', str_replace(' ', '', $search_term))
                              ->orWhere('brand.name', 'LIKE', '%'. $search_term);
                    })->get();

I have made 2 query scopes for the WhereLike and orWhereLike

public function scopeWhereLike($query, $column, $value)
{
    return $query->where($column, 'like', '%'.$value.'%');
}

public function scopeOrWhereLike($query, $column, $value)
{
    return $query->orWhere($column, 'like', '%'.$value.'%');
}

Answer

If they’re using PascalCase (“TurtleWax”), you can do the following:

public function scopeWhereLike($query, $column, $value)
{
    
    return $query
        ->where($column, 'like', '%'.implode(' ', preg_split('/(?=[A-Z])/', $value)).'%') // "word/space case"
        ->orWhere($column, 'like', '%'.ucwords(strtolower($value)).'%') // Titlecase
        ->orWhere($column, 'like', '%'.$value.'%'); // PascalCase
        ->orWhere("REPLACE(".$column.", ' ', '')", 'like', '%'.$value.'%'); // fallback
}

Advertisement

Answer