Unable to pass multiple values in not like clause Laravel

Question

I am working on a Laravel project and want to post multiple values in a not like clause. I have tried the following way with no success.

    $exclude_emails=['odz', 'test.com'];

    $vendors=\DB::table("vendor_infos")
              ->where("vendor_infos.email", 'not like', '%'.$exclude_emails.'%' )
              ->orderByRaw("RAND()")->take(8)->get(['vendor_infos.*']);

I have also tried to pass it as string, but still no success.

Answer 1

You can do this way,

$query = DB::table("vendor_infos");
foreach($exclude_email as $v){
 $query->where("vendor_infos.email",'not like','%'.$v.'%');
}
$vendors = $query->orderByRaw("RAND()")->take(8)->get(['vendor_infos.*']);

I hope this will work

EDIT

Or you can try other way.

$exclude_emails = [
 ['vendor_infos.email' ,'not like','%'.'odz'.'%'],
 ['vendor_infos.email' ,'not like','%'.'test.com'.'%'],
];
 $vendors=\DB::table("vendor_infos")
              ->where($exclude_emails)
              ->orderByRaw("RAND()")->take(8)->get(['vendor_infos.*']);    

Answer 2

You can't use array within a string that is wrapped by apostrophe. There is already '%' character in your third parameter of where function. Why did you use again in your strings?

Try this:

 $vendors=\DB::table("vendor_infos")
          ->where("vendor_infos.email", 'not like', '%odz%')
          ->where("vendor_infos.email", 'not like', '%test.com%')
          ->orderByRaw("RAND()")->take(8)->get(['vendor_infos.*']);

Answer 3

Why not using custom query!

 $exclude_emails=['%odz%', '%test.com%'];
 $exclude_emails = implode('|', $exclude_emails);
 SELECT * FROM  `vendor_infos` WHERE `email` NOT REGEXP '{$exclude_emails}' . . .

Easy and no matter what is the size of $exclude_emails.

Laravel Way:

If you insist to do that with laravel, you can do this :

// making conditions array
foreach($exclude_emails as $e){
  $final_conditions[] = ['email', 'not like', $e];
}

and the query as @AmitGupta answered :

DB::table("vendor_infos")->where( $final_conditions)->orderByRaw("RAND()")->take(8)->get();