Laravel Question posted in
The official Laravel Documentation can be found here.

How to workaround limit of array size when using whereNotIn() in a local scope in Laravel

I have an array which I use to filter out the results of a certain query. There is a known issue where the query returns null, when the passed array in whereNotIn() or whereIn() exceeds a certain amount (I think like +- 1500 records). See: Link

I have already read about some workarounds like using a raw queries or setting the in_predicate_conversion_threshold to 0. But I don’t think the raw query is possible since it is a local scope and you build the query on top on an already existing query and I feel like setting the in_predicate_conversion_threshold to 0 is bad practice and I think it is only possible in a MariaDb database (and not in MySQL, which is what I am using). Also when doing this I get an error saying in_predicate_conversion_threshold is an Unknown system variable.

Now I think the best solution for this problem is to chunk the array in smaller pieces. But I have no idea how to achieve this using the query in a local scope.

public function scopeNotBlacklisted(Builder $query): void
{
    $blacklist = Transaction::where('finalized_at', '>=', now()->subDays(365)->toDateString())
        ->pluck('phonenumber')
        ->all();
    $query->whereNotIn('phonenumber', $blacklist);
}
Tags:

2

Answers


  1. 0

    You can use array_chunk in your scope as below.

    public function scopeNotBlacklisted(Builder $query): void
{
    $blacklist = Transaction::where('finalized_at', '>=', now()->subDays(365)->toDateString())
        ->pluck('phonenumber')
        ->all();

    $chunkedBlacklist = array_chunk($blacklist, 500); // you can adjust count here.

   
    collect($chunkedBlacklist)->each(function ($chunk) use ($query) {
        $query->unless(empty($chunk), function ($query) use ($chunk) {
            $query->whereNotIn('phonenumber', $chunk);
        });
    });
}

    Another option to handle large sets of data for whereIn or whereNotIn clauses is to use temporary tables.

    Login or Signup to reply.
  2. 0

    try using left join instead, then that return result if the join value is null

    public function scopeNotBlacklisted(Builder $query): void {
    $query->leftJoin('transactions as tr', function ( $join ) {
        $join->on('YOUR_WHATEVER_TABLE_HERE.phonenumber', '=', 'tr.phonenumber')
            ->where('tr.finalized_at', '>=', now()->subDays(365)->toDateString());
    })->whereNull('tr.phonenumber');
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search