This content originally appeared on DEV Community and was authored by Bertug Korucu
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.
$transactionIds = Transaction::pluck('id'); // +100k transaction ids
$maxAtOneTime = 5000;
$total = count($transactionIds);
$pages = ceil($total / $maxAtOneTime);
$transactions = collect();
for ($i = 1; $i < ($pages + 1); $i++) {
$offset = (($i - 1) * $maxAtOneTime);
$start = ($offset == 0 ? 0 : ($offset + 1));
$data = Transaction::query()
->whereIn('id', $transactionIds)
->skip($start)
->take($maxAtOneTime)
->get();
$transactions = $transactions->merge($data);
}
// Now $transactions has all you need!
If you like this post, please leave a ?, and if you want to see more of my posts, make sure you click “Follow” ?
See ya!
This content originally appeared on DEV Community and was authored by Bertug Korucu
Bertug Korucu | Sciencx (2021-07-17T14:37:57+00:00) Query +100k records inside whereIn() — Laravel. Retrieved from https://www.scien.cx/2021/07/17/query-100k-records-inside-wherein-laravel/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.