Laravel - Query Model if values contain a certain string (taken from search input)

Question

I am implementing a search using Laravel and Ajax. So I have a Product which belongs to a Tag and a Subcategory. On the other hand the Subcategory belongs to a Category. I want to check all of their properties (field values) and check if they contain the given string. With some searching I found out that I have to use LIKE. Here is what I tried:

$products = Product::where('name_en', 'LIKE', $search)->get();

However this will get the products if the search string matches exactly the value. I want to match if it contains it. How can I proceed with the belongsTo relationships? How can I check the propreties of Tag and Subcategory as well? How to chain everything together so I achieve the desired result? Thanks in advance.

Solution 1

To add to Lakhwinder Singh’s answer, it might be worth wrapping it up in a scope that you can apply to your model:

class Product extends Model
{
    public function scopeSearch($query, $keywords)
    {
        return $query->where('name_en', 'LIKE', '%'.$keywords.'%');
    }
}

You can then use this scope like this:

$products = Product::search($keywords)->get();

Which means you don’t have to keep manually adding “LIKE” conditions throughout your application.

As an aside, Laravel’s introducing Scout, a driver-based full text search extension for Eloquent, in version 5.3.

Solution 2

What you want is to write an advanced query to search product based on related models too, so as previous suggestion by others, you have to write join statements.

Check my example code below, which is written to search members, the search string also will bring members if the string matches, members skills or positions, so this will surely help you.

$users = User::select('app_users.*')
            ->distinct()
            ->join('app_members', 'app_users.id', '=', 'app_members.app_users_id')
            ->leftJoin('app_members_jobtitles', 'app_members.id', '=', 'app_members_jobtitles.app_members_id')
            ->leftJoin('app_jobtitles', 'app_members_jobtitles.app_jobtitles_id', '=', 'app_jobtitles.id')

            ->leftJoin('app_members_tags', 'app_members.id', '=', 'app_members_tags.app_members_id')
            ->leftJoin('app_technologies', 'app_members_tags.app_technologies_id', '=', 'app_technologies.id')
            ->whereNull('app_users.activation')
            ->where('app_users.block','=',0)
            ->where(function ($query)use ($search) {
                $query->orWhere('app_users.first_name', 'like', '%'.$search.'%')
                      ->orWhere('app_users.last_name', 'like', '%'.$search.'%')
                      ->orWhere('app_members.company', 'like', '%'.$search.'%')
                      ->orWhere('app_members.job_title', 'like', '%'.$search.'%')
                      ->orWhere('app_jobtitles.title', 'like', '%'.$search.'%')
                      ->orWhere('app_technologies.title', 'like', '%'.$search.'%')
                      ->orWhere('app_members.summary', 'like', '%'.$search.'%');
            })
            ->get();

Note the following join in the above code, which is in your case category and sub category

->leftJoin('app_members_jobtitles', 'app_members.id', '=', 'app_members_jobtitles.app_members_id')
        ->leftJoin('app_jobtitles', 'app_members_jobtitles.app_jobtitles_id', '=', 'app_jobtitles.id')