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()
, youll 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
Ill call this the bad because I personally dont 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();
Im pretty sure you too feel like:
The Elegant Way
Now lets 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!
Original Link: https://dev.to/kodeas/query-100k-records-inside-wherein-laravel-50n3
Dev To
An online community for sharing and discovering great ideas, having debates, and making friendsMore About this Source Visit Dev To