How to use WHERE NOT IN in DB:Raw Laravel

Question

How to use WHERE NOT IN in DB:raw Laravel?

Here's my sample code

$query = DB::select(DB::raw("... WHERE ID NOT IN (:ids) ..", array( 'ids' => "1,2,3" )));

I also tried this

$ids = "'1','2','3'";
$query = DB::select(DB::raw("... WHERE ID NOT IN (:ids) ..", array( 'ids' => $ids )));

Answer 1

You would need to do use setBindings to bind your params like below

$query = DB::select(DB::raw("... WHERE ID NOT IN (:ids) .."))
    ->setBindings(['ids' => $ids]);

Answer based on docs https://laravel.com/api/10.x/Illuminate/Database/Query/Builder.html#method_setBindings

Answer 2

I suppose the error, "Unknown column 'ids' in 'where clause'", suggests that the database engine is treating 'ids' as a column name rather than as a parameter placeholder.

The below change can probably work:

$query = DB::select(DB::raw("... WHERE ID NOT IN (?)"));
$results = $query->setBindings([$ids]);

Answer 3

Got it. Thank you for your answers, but I use this solution:

$ids = [1,2,3];
//ids = [''] if ids is empty
$bindStr = trim(str_repeat('?,',count($ids)),',');
$query = DB::select(DB::raw("... WHERE ID NOT IN ({$bindStr})"), $ids);