Ad

Querying 65k+ Records With WhereIn

- 1 answer

I have an export functionality and I want to query and export using given ids. I found a solution of querying using skip() and take() but when now whereIn('id', $ids) giving me the error as it has 85k records as of now.

PDOException: SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders

The solution I was using was:

public function handle() {
    $max = 5000;
    $total = $this->givenIds->count();

    $pages = ceil($total / $max);

    for ($i = 1; $i < ($pages + 1); $i++) {
        $offset = (($i - 1) * $max);
        $start = ($offset == 0 ? 0 : ($offset + 1));

        MyModel::whereIn('id', $givenIds)
                 ->orderBy('created_at', 'desc')
                 ->skip($start)
                 ->take($max)
                 ->get();

       // $this->generateTempCsv();
    }

    // $this->combineTempCSVs()
}

This was working fine without bottlenecking - until the too many placeholder error. Now that I received this error, I tried:

$query = MyModel::orderBy('created_at', 'desc')

foreach ($this->givenIds->chunk(2000) as $i => $chunk) {
    if ($i == 0) {
       $query = $query->whereIn('id', $chunk);
    } else {
       $query = $query->orWhereIn('id', $chunk);
    }
}

This gives the same error.

If I want to query using givenIds, and still want to keep "orderBy - created_at" without bottlenecking what should I do?

The givenIds come from Controller (as above code is Job):

$givenIds = MyModel::select('id', 'created_at')
                    ->where(function($q) use ($dateRange, $queries) {
                            $this->applyQueryFilters($q, $queries, $dateRange);
                     })
                     ->orderBy('created_at', 'desc')
                     ->pluck('id');

Exporter::dispatch($givenIds)->onQueue('export');

PS: The reason I use $givenIds is because there are filters and first I get related ids after applying the filters and then passing the $givenIds into the job. Do I need to sort by id when getting $givenIds and depending the order from there?

Ad

Answer

Try this: in your database config file, under sql or the driver you are using to connect to your database, add a field options like this

'options'=> [PDO::ATTR_EMULATE_PREPARES => true]

PHP API infact says (check this):

Some drivers do not support native prepared statements or have limited support for them.

Another solution should be to change the driver, but i suppose that adding a line to the database.php is easier to do

Possible side effect reported: this may cast the return values to string, which will effect your code if you are using === as comparison operator. You can solve this using the protected $casts variable in the models (if you are using Laravel)

Ad
source: stackoverflow.com
Ad