Photo by vackground.com on Unsplash

SQLSTATE[IMSSP]: Tried to bind parameter number 2101

And how to approach it, in Laravel

You might have encountered this error if you are working with a SQL Server database.

It can occur when you are trying to use the whereIn clause with more than 2100 elements inside the array:

To prevent this from happening, we can do multiple queries and join the results at the end.

We can assume that the query remains unchanged, all we have to do is write the logic in the service.

First, we chunk them, you can either hardcode it or set it as a configurable value:

Next, we go through every chunk:

In the next step, we need to call the repository and retrieve our users:

Fun fact

You can pass a Collection to the whereIn() method as the Builder will automatically convert it to an Array if it’s an instance of Arrayable

Collection implements Enumerable which implements Arrayable

Well, we did all we had to… but… we haven’t joined our data. My favorite method is to use the reduce method of Laravel.

And now, we can merge the $carry value with what we get from the repository.

And this should be it. This is one of many ways to solve it.

If you are using Models there are other ways to do it. But if you are using the query builder, this is a solid way to do it.