CODEWITHSUNDEEP
mysqllaraveleloquent
zarpio
zarpio

Reputation: 7338

1390 Prepared statement contains too many placeholders using Laravel 5.6

Getting following error

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

My query has become due to data in the table recently

SELECT
  *
FROM
  table
WHERE col1 = 'some-val'
  AND col2 NOT IN ('va1', 'val2', 80k values... )

I am using Laravel 5.6 

$data_will_be_skipped = OtherModel::select('code')
        ->where('col1', 0)
        ->orWhere('col2', 1)
        ->groupBy('col3')
        ->pluck('col3')->toArray();

$data_will_be_skipped is now approx 80k arrays

Model::where('col1', 'some-val')->whereNotIn('col2', $data_will_be_skipped)->get();

Upvotes: 1

Views: 8814

Answers (2)

Manish Gupta
Manish Gupta

Reputation: 105

No matter how many items u have in array to check in wherein

the simple way to do it

$data = model::whereRaw("id not in (".implode(',',$data_will_be_skipped).")")->get();

Upvotes: 0

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

MySQL has a limit of 65,535 parameters in prepared statements.

Use a subquery instead:

$data_will_be_skipped = OtherModel::select('col3')
    ->where('col1', 0)
    ->orWhere('col2', 1);
Model::where('col1', 'some-val')
    ->whereNotIn('col2', $data_will_be_skipped)
    ->get();

Upvotes: 3

Related Questions