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

2

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++;
}

edited Dec 4, 2014 at 17:00

answered Dec 4, 2014 at 16:29

Tim Lewis

29.5k14 gold badges82 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

baao Over a year ago

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?

Tim Lewis Over a year ago

Hmm... It shouldn't be like that. Let me make a quick edit to show an example.

baao Over a year ago

Yeah, I thought concat would find everything, too.

baao Over a year ago

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.

Tim Lewis Over a year ago

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related