Your Web News in One Place

Help Webnuz

Referal links:

Sign up for GreenGeeks web hosting
July 17, 2021 02:37 pm GMT

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:

DuckTape

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

Share this article:    Share on Facebook
View Full Article

Dev To

An online community for sharing and discovering great ideas, having debates, and making friends

More About this Source Visit Dev To