laravel 5.3 raw SQL statement in Builder

Question

I have applied a laravel builder to filter search input by user in a form, but it isn't working optimal. This is my scenario:

my form looks like this:

in the first input is custom query search where the following builder is made.

return $builder->where('city', 'LIKE', '%' . $value . '%')
                   ->orWhere('first_name', 'LIKE', '%' . $value . '%')
                   ->orWhere('middle_name', 'LIKE', '%' . $value . '%')
                   ->orWhere('last_name', 'LIKE', '%' . $value . '%');

the 3 dropdown menu's are filtered by the following code:

return $builder->where('location', $value); // <-- for "selecteer locatie"
return $builder->where('level', $value); // <-- for "selecteer richting"
return $builder->where('graduation', $value); // <-- for "selecteer diplomajaar"

$value is the input of the user.

Now whenever I filter by the 3 dropdown options, it works as magic. Whenever i try to combine it with the Query of the first filter, it shows more results than i actually want.

So the first query is giving an OR-statement and that actually is the bug. I want to place a RAW SQL statement in the builder where I can run

 "WHERE `city` LIKE %" . $value . "% 
 OR `first_name` LIKE %" . $value . " 
 OR `middle_name` LIKE %" . $value . " 
 OR `last_name` LIKE %" . $value . " 

in between these symbols ( 'statement' )

Is there a way where i can run the builder separately or a way how I can input a raw SQL statement in the laravel 5.3 builder?

If you need more information, please ask.

EDIT: My question is not clear enough! see description below.

I am using the Builder class to make my SQL filter in my form. Whenever I run the advanced search and dump my Builder, I get a Builder object with the following array in the +where clause:

+wheres: array:7 [▼
0 => array:5 [▼
  "type" => "Basic"
  "column" => "city"
  "operator" => "LIKE"
  "value" => "%hof%"
  "boolean" => "and"
]
1 => array:5 [▼
  "type" => "Basic"
  "column" => "first_name"
  "operator" => "LIKE"
  "value" => "%hof%"
  "boolean" => "or"
]
2 => array:5 [▼
  "type" => "Basic"
  "column" => "middle_name"
  "operator" => "LIKE"
  "value" => "%hof%"
  "boolean" => "or"
]
3 => array:5 [▼
  "type" => "Basic"
  "column" => "last_name"
  "operator" => "LIKE"
  "value" => "%hof%"
  "boolean" => "or"
]
4 => array:5 [▼
  "type" => "Basic"
  "column" => "location"
  "operator" => "="
  "value" => "Franeker"
  "boolean" => "and"
]
5 => array:5 [▼
  "type" => "Basic"
  "column" => "level"
  "operator" => "="
  "value" => "MAVO"
  "boolean" => "and"
]
6 => array:5 [▼
  "type" => "Basic"
  "column" => "graduation"
  "operator" => "="
  "value" => "1992"
  "boolean" => "and"
]
]

Because I am using an OR-operator for comparing with city , first_name , middle_name or last_name, I should be getting 4 where clauses instead of 7. This is caused because the Builder sees the OR-statement called in the first input as an AND-statement.

Answer 1

Your answer should correctly group the wheres on their own.

For "how I can input a raw SQL statement in the Laravel 5.3 builder?"

DB::statement($rawStatement)

Make sure you bind the values as the second parameter to statement, you can also use

DB::statement(DB::raw($rawStatement))

There's also

 $query->whereRaw("city LIKE %{$value}%")

Which can be useful for entering some queries but would not solve your nested query issue.

If you wanted to make all of the wheres raw you would have to do something along the lines of :

$query->whereRaw($mainQuery)
->setBindings([$city, $first_name, $middle_name,  $last_name])

Where main query looks like:

"(city LIKE ? OR first_name LIKE ? OR middle_name LIKE ? OR last_name LIKE ?)"

Then you can append your raw filtered query to that string

"AND location = ?"

Answer 2

I solved it by replacing this:

return $builder->where('city', 'LIKE', '%' . $value . '%')
                   ->orWhere('first_name', 'LIKE', '%' . $value . '%')
                   ->orWhere('middle_name', 'LIKE', '%' . $value . '%')
                   ->orWhere('last_name', 'LIKE', '%' . $value . '%');

into this:

return $builder->where( function($query) use ($value){

    $query->where('city', 'LIKE', '%' . $value . '%')
          ->orWhere('first_name', 'LIKE', '%' . $value . '%')
          ->orWhere('middle_name', 'LIKE', '%' . $value . '%')
          ->orWhere('last_name', 'LIKE', '%' . $value . '%');
});

EDIT: the following +where clauses is dumped when die and dumping the Builder class

+wheres: array:4 [▼
 0 => array:3 [▼
  "type" => "Nested"
  "query" => Builder {#216 ▶}
  "boolean" => "and"
 ]
 1 => array:5 [▼
  "type" => "Basic"
  "column" => "location"
  "operator" => "="
  "value" => "Franeker"
  "boolean" => "and"
 ]
 2 => array:5 [▼
  "type" => "Basic"
  "column" => "level"
  "operator" => "="
  "value" => "MAVO"
  "boolean" => "and"
 ]
 3 => array:5 [▼
  "type" => "Basic"
  "column" => "graduation"
  "operator" => "="
  "value" => "1992"
  "boolean" => "and"
 ]
]

In the nested Builder class at array index 0 is the OR-statement inserted.