Laravel query builder with array input

Question

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

Tim Lewis · Accepted Answer · 2022-04-22 19:43:54Z

1

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%')

edited Apr 22, 2022 at 19:43

answered Apr 22, 2022 at 18:46

Tim Lewis

29.5k14 gold badges82 silver badges109 bronze badges

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

6 Comments

Ali Mogh Over a year ago

This eloquent returns some extra rows that I do not want to be in $books.

Tim Lewis Over a year ago

The query I wrote is exclusive; if name1 or name2 matches any of the values in $words, it will be returned. Your original query is inclusive, in that name1 or name2 must match word1, word2 and word3. Is that correct? I can make that adjustment, but it seems odd.

Ali Mogh Over a year ago

Yes, correct. Thank you for the edit. But it has an error. where have I define $query? I see this error: Undefined variable '$query'

Tim Lewis Over a year ago

Woops; $query = Books::where('is_active', 1');, then $query->where(function ...), then finally $books = $query->get();; messed up the variable naming.

Ali Mogh Over a year ago

Thank you very much. It is working now and returns desired data.

|

Collectives™ on Stack Overflow

Laravel query builder with array input

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related