Laravel select query with array

Question

I get a string like this

$str = 'What is a typical day like';

Now I want to search the DB for every word this string contains.

What
is
a
...

I'm trying the following:

$var = Input::get('search');
// first try was $data = explode(' ',$var);
$data = array(str_replace(' ',',',$var));

$result = Faq::whereIn('heading', $data)->orWhereIn('wording',$data)->get();

return Response::json(array('name' => $result));

Both gives me no results, but each of those words is at least 4 times in the database. Where is my error?

but you heading do not match with exact value like is, a etc thats why you need to use where('heading', 'LIKE', '%value%')->get() something like this, not exactly — Anand Patel
– Anand Patel, Commented Dec 4, 2014 at 16:29

Tim Lewis · Accepted Answer · 2014-12-04 17:00:47Z

explode() is the right function to use here, as it will split all your words into an array properly. This will only not work if you're looking for (as an example) a name like "Mary Ann" or something with a space in it. As for the query, if you're trying to search every column for every word, that might be a little tricky... BUT you could do something like this:

$query = Faq->select(); // Initializes the query
foreach($data AS $value){
  $query->where(DB::raw("CONCAT_WS(' ', column_1, column_2, ...)"), 'LIKE', '%'.$value.'%');
}
$faqs = $query->get(); // Runs the query

The logic behind that is CONCAT_WS on all the columns to create a giant string of all the values of all the colmuns, then querying that string to match each word in your $data array.

This kind of query (ie searching/filtering results based on input) is much easier when you know what column you're looking for. An associative array of column_name => value_to_find makes this much easier as you can do:

foreach($data AS $key => $value){
  $query->where($key, 'LIKE', '%'.$value.'%');
}
$faqs = $query->get();

Edited Example

+----------+----------+----------+
| address  | city     | province |
+----------+----------+----------+
| 123 Main | Toronto  | Ontario  |
| 123 2nd  | Montreal | Quebec   |
+----------+----------+----------+

Given this test data, calling CONCAT_WS(" ", address, city, province)) in your SQL would result in a string of 123 Main Toronto Ontario and 123 2nd Montreal Quebec. If your search array was something like [0] => 123, [1] => Toronto, [2] => Ontario I think you'd (maybe?) get 1 result as $query->where(...) followed by another $query->where(...) produces a WHERE ... AND WHERE ... structure. As I mentioned in my edit, add a counter to the foreach:

$counter = 0;
$query = Faq->select(); // Initializes the query
foreach($data AS $value){
  if($counter == 0){
    $query->where(DB::raw("CONCAT_WS(' ', column_1, column_2, ...)"), 'LIKE', '%'.$value.'%'); 
  } else {
    $query->orWhere(DB::raw("CONCAT_WS(' ', column_1, column_2, ...)"), 'LIKE', '%'.$value.'%'); 
  }
  $counter++;
}

This is the perfect query for narrowing down the searches +1 and thank you, but (with the little tests I just did), it seems like both words I search for need to be in the string. Is there a possibility to find all the strings occuring in whatever column? What do you think?
Hmm... It shouldn't be like that. Let me make a quick edit to show an example.
As an example, if I search for "What" only, I get 6 results, but if I search for "What RBC" I only get 3 results, though I excpected at least 6, too.
Well, the query would be structed as WHERE ... AND WHERE ... as that's what Laravel does. To do an OR WHERE you'd need a counter in your foreach that, after the first result, would change $query->where(...) into $query->orWhere(...)

Md.Sukel Ali · Accepted Answer · 2019-04-16 12:21:53Z

0

 public static function getProductSearch($words){

        $word = explode(' ', $words);

        $value = Product::where('active', 'Y');
        foreach ($word as $key) {
            $value->where('title', 'like', "%$key%");
        }
            $data = $value->get();
        return $data;
    }

edited Apr 16, 2019 at 12:21

Md.Sukel Ali

3,0655 gold badges26 silver badges36 bronze badges

answered Apr 16, 2019 at 11:12

Jacek

1

Collectives™ on Stack Overflow

Laravel select query with array

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related