0

I am currently working in search based on relevancy of keyword. I have customers and their following fields.

 1. name
 2. designation
 3. address
 .
 ...

When search is made with keyword "Computer Engineer" Then My search displays customers in following order of matching,

1. both keyword match
2. first keyword match
3. second keyword match

In this way, I can get the list of customers.

I want to use the customer_id of search result list in other query like:

Model::whereIn('customer_id', $searchResultCustomerIds)->get();

There may be any number of customers in the search result based on no of customers in database. What is the maximum limit of size of array that can be used in eloquent whereIn clause ?

Vaibhavi S.
  • 1,083
  • 7
  • 20
Sagar Gautam
  • 9,049
  • 6
  • 53
  • 84
  • 1
    I think array in Eloquent whereIn clause is no different from a PHP array. [This](https://stackoverflow.com/questions/6856506/what-is-the-maximum-size-of-an-array-in-php) and [this](https://stackoverflow.com/questions/3036957/php-do-arrays-have-a-maximum-size) discuss about maximum size of PHP array possible. – nice_dev Dec 27 '19 at 07:46
  • 1
    As much as PHP is allowed, usually 128 MB. [MySQL can handle it](https://stackoverflow.com/questions/4275640/mysql-in-condition-limit/4275704) – Levente Otta Dec 27 '19 at 07:47
  • @LeventeOtta 128 MB means ? If I have one dimentional array with integer values then what is the approximate array size ? – Sagar Gautam Dec 27 '19 at 07:54
  • @SagarGautam If the array contains 100,000 unique elements, then in principle less than 1 MB of memory is used. – Levente Otta Dec 27 '19 at 08:18
  • On php side there's a memory limit specified in your php.ini. On mysql side there's also a limit specified in `max_allowed_packet`. The answers to [this question](https://stackoverflow.com/questions/4514697/mysql-in-operator-performance-on-large-number-of-values) contain some additional info on large where in queries. – PtrTon Dec 27 '19 at 09:01
  • there could be a parameter limit on the db side, potentially but it would be pretty high – lagbox Dec 27 '19 at 15:47
  • it's (2^16 -1) which is 65,535 , for details you can refer to https://stackoverflow.com/questions/18100782/import-of-50k-records-in-mysql-gives-general-error-1390-prepared-statement-con – suraj mishra Oct 18 '21 at 10:42

1 Answers1

0

Around 1500 entries. I think that is a Laravel limitation on eloquent. If the whereIn query return 0 results, do a whereRaw instead, like the following: (supposing the wherein is on id column)

Obs: NEVER use $query->get() on a extremely large query result. If you're expecting a big return, paginate or do a cursor().

// using cursor, for each item, laravel relases the item from the memory, if you do a ->get(), laravel stores everything at memory, you can down your server doing that.

foreach($query->cursor() as $item) {}

// continuing

$ids = [1,2,3,4,........ 9000,9001,9002];
$query = Model::query();
$rawwhereIn = 'id IN (\'' . implode('\',\'', (array)$ids) . '\')';
$query->whereRaw($rawwhereIn);

foreach($query->cursor() as $item) {
   echo "$item->id found";
}
Henrique Troiano
  • 44
  • 4