Creating Laravel Eloquent Query with unknown number of parameters

Question

I'm trying to work out how to create an Eloquent query dynamically using user input which changes the query depending on the length of input. Using straight MySQL this is simple ... just concatenate a query string based on user input. Using Eloquent that's not possible, unless you either a) use a 'raw' sql query, which defeats the purpose of using Eloquent or b) Use something highly unhealthy like an eval() function.

How can you iterate through user input of unknown length and create an Eloquent query that searches multiple fields for words using combinations of 'OR' and 'AND'

Consider the following pseudocode which can't actually work.

$search = "cat sat on the mat";
$searchWords = explode(' ', $search);

$data['finds'] = DB::table('mytable') [... incomplete query]


foreach ($searchWords as $word) {
     ->where('firstname', 'like', '%' . $word . '%')
     ->orWhere('lastname', 'like', '%' . $word . '%')
     ->orWhere('address', 'like', '%' . $word . '%')->AND [... next word]
}

Or is it simply a matter of giving up and using a raw query? I could run a separate query for each word, and then use PHP to merge/manipulate the results, but that seems very cumbersome for what can be done in one query in traditional MySQL.

A traditional MySQL query for what I'm trying to do would be:

SELECT * FROM `mytable`
WHERE (firstame LIKE '%$cat%' OR lastname LIKE '%cat%' OR address LIKE '%cat%')
AND (firstname LIKE '%sat% OR lastname LIKE '%sat%' OR address LIKE '%sat%')
AND [etc, etc, for all words in user input]

Answer 1

You can group all the where's in the loop inside a "parent" where.

$query = DB::table('mytable');

foreach ($searchWords as $word) {
     $query->where(function ($query) use ($word)
     {
         $query->orWhere('firstname', 'like', '%' . $word . '%');
         $query->orWhere('lastname', 'like', '%' . $word . '%');
         $query->orWhere('address', 'like', '%' . $word . '%');
     });
}

$results = $query->get();

This will result in a SQL query similar to the one you wanted. You can check the raw SQL result by printing out $query->toSql().