Laravel Search Keyword Query

user_8505034 0
Viewed 1148

So currently I've created search text field to find keywords for specific columns of my database.

Using this eloquent

$data = DB::table('product')
        ->select('product.id','product.title','product.description','product.content','product.photo','product.quantity','product.price')
        ->join('product_per_category','product_per_category.product_id','=','product.id')
        ->where(['product.deleted' => 0])
        ->where(['product_per_category.deleted' => 0])
        ->where(['product_per_category.productcategory_id' => $id])
        ->where(function($query) use ($keyword){
            $query->where('product.content', 'like', '%' . $keyword . '%')
                ->orWhere('product.title', 'like', '%' . $keyword . '%')
                ->orWhere('product.quantity', 'like', '%' . $keyword . '%')
                ->orWhere('product.price', 'like', '%' . $keyword . '%');
        })
        ->groupBy('product.id')
        ->paginate(10);

If I try to search 234.00 value of Price all possible value that will match with the columns of content, title, quantity, price, quantity will automatically return the data.

How ever if I tried to search for a keyword like this

234.00 Books value of Price and Title. it will not return any data now. Because it will match only this sentence on specific columns.

What I'm trying to do is if I search something like this

= 234.00 Books 20

*Value of Price, Title and Quantity

It should return all the data that will match on every columns. Whether the letters lowercase or uppercase as long as it matches the position of letters

Is that possible?

** UPDATE **

Objective : Just want to match the 234 value on any columns. Be the result. But the problem of my query is, if I add another keyword like 234 Books it doesnt return any data

laravel mysql php
2 Answers

First: Extract the keywords:

$delimeter = ' '; //or your separator
$keywords = explode($delimeter, $search); // Will return an array containing each keyword

Then: Loop over each keyword and call your search query or expand your query dynamically

Example Something like this should work...

$base_query = DB::table('product')
        ->select('product.id','product.title','product.description','product.content','product.photo','product.quantity','product.price')
        ->join('product_per_category','product_per_category.product_id','=','product.id')
        ->where(['product.deleted' => 0])
        ->where(['product_per_category.deleted' => 0])
        ->where(['product_per_category.productcategory_id' => $id]);

foreach($keywords as $keyword){
       $base_query->where(function($query) use ($keyword){
            $query->where('product.content', 'like', '%' . $keyword . '%')
                ->orWhere('product.title', 'like', '%' . $keyword . '%')
                ->orWhere('product.quantity', 'like', '%' . $keyword . '%')
                ->orWhere('product.price', 'like', '%' . $keyword . '%');
            })
}

$result = $base_query->get();

You will need to do a loop because you don't know the amount of keywords in your search result

user_34858770

You can use explode PHP function like this:

$keywords = explode(' ', $keyword); // Will return an array containing each keyword
dd($keywords);

If your keywords can contain spaces, you may should consider using another delimiter or splitting your query parameters.

user_138346600
Related