When you use the whereIn clause in Laravel Eloquent, you might end up hitting the maximum number of parameters that your database engine supports. Depending on the database engine you use, the maximum number of parameters it supports might be as low as 2100 (Microsoft SQL Server).

On top of that, there is an old bug in the PDO implementation for MySQL which can cause these types of queries to be very slow.

If you are using an array of integers in the clause, you're lucky. In that scenario, you can use the whereIntegerInRaw function instead.

So, instead of doing this:

<?php

User::whereIn('id', [1, 2, 3, 4, 5])->get();

You can simply use:

<?php

User::whereIntegerInRaw('id', [1, 2, 3, 4, 5])->get();

The difference is in the way the SQL statement is generated.

When using whereIn, the SQL will become (needing 5 placeholders):

SELECT * FROM users WHERE id in (?, ?, ?, ?, ?)

When using whereIntegerInRaw, the SQL becomes (values are in the statement, no placeholders needed):

SELECT * FROM users WHERE id in (1, 2, 3, 4, 5)

The whereIntegerInRaw method ensures that the values of the array are integers, which ensures there are no SQL injection vulnerabilities. This is the reason why this workaround only works for an array of integers. This shouldn't be a problem as using integers in a whereIn clause probably covers the majority of the use cases.

Related Posts

  • Customising lazy-loading prevention in Laravel
  • The giveConfig function in Laravel
  • How to get request parameters in Laravel
  • Laravel Service Provider in Simple English
  • Testing for equal json strings in Laravel tests