🔥Query +100k records inside whereIn() — Laravel

If you are tinkering with a big data (say are trying to export a huge CSV), you may end up needing to query +100k ids in a whereIn().

And if you try it to use +100k ids in a whereIn(), you’ll see a MySQL error “General error: 1390 Prepared statement contains too many placeholders”.

$my100kIds = [1,2,3,4,5, ...];
Transaction::whereIn('id', $my100kIds)->get();

This is because you can use 65,536 placeholders at a time in MySQL.

Well, you are not the only one facing this problem! Good news is, here is a way to overcome that problem.

The Quick and Dirty Way

I’ll call this the bad because I personally don’t like changing MySQL configs in the runtime, but it works.

$my100kIds = [1,2,3,4,5, ...];
config([
    'database.connections.mysql.options' => [ 
      \PDO::ATTR_EMULATE_PREPARES => true
    ]
]);
\Illuminate\Support\Facades\DB::reconnect();
$result = Transaction::whereIn('id', $my100kIds)->get();

I’m pretty sure you too feel like:

The Elegant Way

Now let’s see the good way of achieving this.

If you like this post, please give me a 👏 , and if you want to see more of my posts, make sure you click “Follow” 😊

See ya!