longlife's avatar

WhereIn array length limited?

Hi, i got a problem when the array size of "$array_id" of this query goes over 1000.

     $services = Service::whereIn('id', $array_id)->get();

This just return 0 instance of Service when the $array_id contains more than 1000 id, at 999 array size the query works just fine. I got this problem only on my production server, it works as expected in local enviroment. Im guessing of some bad configuration over php or mysql, some one can help me?

0 likes
6 replies
Sinnbeck's avatar

Sounds like you should look into a join or whereHas instead. What is $array_ids ? The issue is most likely from you sql server that has a max of 1000 bound parameters

1 like
longlife's avatar

@Sinnbeck Thanks for the quick response, i will try with a join but i would like to know which parameter limits the wherein query array size, do you have any idea?

Sinnbeck's avatar

I am not sure how or even if you can change it. If I recall correctly, mariadb switches to using a materialized view when hitting a 1000 binds. But even if you can change it, my suggestion is to just join/whereHas due to perfomance.

1 like
Safaetul's avatar

$services = Service::whereIntegerInRaw('id', $array)->get();

Amir-16's avatar

when you have to check large data use whereintegerinraw instead of whereIn ... It also give result much faster...

Please or to participate in this conversation.