在Laravel的Eloquent whereIn方法中的原始声明

Question

I would simply like to run such query:

select * from `users` where SUBSTRING_INDEX(`email`, '@' ,-1) not in ('gmail.com, outlook.com');

Two ways crossed my mind which non of them work:

1

$providers = array('gmail.com', 'outlook.com');

$providers = "'" . implode("', '", $providers) . "'";

User::whereRaw("SUBSTRING_INDEX(`email`, '@' ,-1) not in (?)", $providers);

the above would not work because PDO will escape the "'" characters.

2

User::whereIn(DB::raw("SUBSTRING_INDEX(`email`, '@' ,-1)", $providers);

this one simply does not work. any idea?

Answer 1

Here's a safer way to do it:

$providers = ['gmail.com', 'outlook.com'];

$placeholder = implode(', ', array_fill(0, count($providers), '?'));

User::whereRaw("SUBSTRING_INDEX(`email`, '@' ,-1) not in ($placeholder)", $providers);