How to use array with LIKE with whereIn to retrieve eloquent rows in Laravel

Question

I have an array

//dynamically generated. dynamic number of elements
$keywords = ['google', 'youlense'];

For a exactly matching values of $keywork mapping to row in content column, i can do following:

$result = \App\Table::where(function($query){
    $query->whereIn('content', $keywords);
});

and the result would be somewhat

select * from tables where content IN ('google', 'youlense');

but I want to use LIKE operator so the result may be like

select * from tables where content LIKE ('%google%', '%youlense%');

I know that is not allowed in mysql but can some one recommend a simple and clean technique to handle this

Andrej · Accepted Answer · 2016-09-01 18:21:48Z

6

You can simply use orWhere method for every keyword and it will equivalent for whereIn. The code will look like:

$result = \App\Table::where(function($query){
    $query->orWhere('content', 'LIKE', '%google%')
          ->orWhere('content', 'LIKE', '%youlense%')
          -> and so on;
});

$result = \App\Table::where(function($query) use($keywords){
    foreach($keywords as $keyword) {
        $query->orWhere('content', 'LIKE', "%$keywords%")
    }
});

Note: gotten query can work very slowly.

edited Sep 1, 2016 at 18:21

answered Sep 1, 2016 at 18:10

Andrej

7,5041 gold badge22 silver badges22 bronze badges

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

7 Comments

Eirtaza Over a year ago

thanks but that would work if i knew there are only two elements. In real scenario, this array would be dynamically generated and there would be dynamic number of items. My mistake i didn't mention that.

Andrej Over a year ago

Sure, just use foreach. I will update the answer. But it can be slow. @Eirtaza

Eirtaza Over a year ago

number of queries run = elements in $keyword... m i right?

Andrej Over a year ago

No, number of conditions in one query.

Eirtaza Over a year ago

check the answer below... it would be 1 query. Both answers are 100 % same i dont know which one to mark as correct answer. stuck

|

Ahmad Hajjar · Accepted Answer · 2016-09-01 18:32:50Z

1

You can write a function that will basically do the following :

public function searchByKeywords(array $keywords = array()){
    $result = \App\Table::where(function($query) use ($keywords){
         foreach($keywords as $keyword){
              $query = $query->orWhere('content', 'LIKE', "%$keyword%");
         }
         return $query;
    });
    return $result->get(); // at this line the query will be executed only
                           // after it was built in the last few lines
}

edited Sep 1, 2016 at 18:32

answered Sep 1, 2016 at 18:22

Ahmad Hajjar

1,8533 gold badges20 silver badges33 bronze badges

4 Comments

Eirtaza Over a year ago

if there are three elements in $keyword, how many mysql queries would it run? 1 or 3

Ahmad Hajjar Over a year ago

one, because this is not running queries, this is only building it, later when you get the result the query will be executed all at once

Eirtaza Over a year ago

i agree, i am stuck which answer to mark as correct. please decide among urselves with andrej

Ahmad Hajjar Over a year ago

I updated the answer , never mind man :) it is ok just accept his answer since he wrote it first :)

Collectives™ on Stack Overflow

How to use array with LIKE with whereIn to retrieve eloquent rows in Laravel

2 Answers 2

7 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related