Laravel选择带数组的查询

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?

Answer 1

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