CODEWITHSUNDEEP
mysqllaraveleloquentlaravel-query-builder
Ali Mogh
Ali Mogh

Reputation: 11

Laravel query builder with array input

I want to write an eloquent in Laravel, but I don't know how.

I have an array which contains some words:

$words = ["word1", "word2", "word3", ...]

And I have a table like this:

+--+--------+--------+----------+
|id| name1  |name2   |is_active |
+--+--------+--------+----------+
| 1| smthng1| anthng1| 1        |
| 2| smthng2| anthng2| 1        |
| 3| smthng3| anthng3| 0        |
| 4| smthng4| anthng4| 1        |

I want to write an eloquent which is equal to this query:

select * from `books` 
where `is_active` = 1 
and (`name1` like "%word1%" or `name2` like "%word1%") 
and (`name1` like "%word2%" or `name2` like "%word2%")
and (`name1` like "%word3%" or `name2` like "%word3%")

I have such an eloquent if the $words is just a single string:

$books = Books::where('is_active','1')->where(
                    function($query) use ($searchWords){
                        $query->where('name1', 'like', "%$searchWords%")
                            ->orWhere('name2', 'like', "%$searchWords%");
                    }
                    )
                ->get();

But I don's know what to do if the $words is an array of strings.

Thanks in advance

Upvotes: 0

Views: 321

Answers (1)

Tim Lewis
Tim Lewis

Reputation: 29278

You'll just need another nested foreach() loop:

$words = ["word1", "word2", "word3"];
$books = Books::where(function ($query) use ($words) {
  foreach ($words as $word) {
    $query->orWhere('name1', 'like', "%{$word}%")
    ->orWhere('name2', 'like', "%{$word}%");
  }
})->where('is_active','1')
->get();

Your WHERE Subquery should then look something like:

(
 WHERE name1 LIKE '%word1%' OR name2 LIKE '%word1%'
 OR name1 LIKE '%word2%' OR name2 LIKE '%word2%'
 OR name1 LIKE '%word3%' OR name2 LIKE '%word3%'
 -- And so on for each `$word`
)

Edit

Inclusive Query would be:

$query = Books::where('is_active', 1');
foreach ($words as $word) {
  $books = $query->where(function ($subQuery) use ($word) {
    $subQuery->where('name1', 'LIKE', "%{$word}%")-
    ->orWhere('name2', 'LIKE', "%{$word}%");
  });
}
$books = $query->get();

That would produce the SQL:

WHERE (name1 LIKE '%word1%' OR name2 LIKE '%word1%') 
AND (name1 LIKE '%word2%' OR name2 LIKE '%word2%') 
AND (name1 LIKE '%word3%' OR name2 LIKE '%word3%')

Upvotes: 1

Related Questions