One eloquent query with whereIn clause with more than 3000 elements works but another one with the same elements and format doesn't

Question

Hello and thank you beforehand for your help. I've been hitting my head against a wall with this problem for a few days now so decided to ask here. I have two queries in Laravel, one grouping totals by week, and the other by month. The week one works fine but for some reason the month one doesn't, the only difference in essentially the query is that the weekly one is calculated yearly but in a different period (starting in week 48 of last year and ending in week 47 of this year), while the monthly is just the real year. The only other difference is that the week query is inside an if to show the right thata in those final weeks of the year.

$weeklySalesLastYear = Invoice::where(function ($query) use ($year, $client_ids){
            $query->where('year', $year-2)->where('week', '>=', 48)->whereIn('client_id', $client_ids);
        })->orWhere(function($query) use ($year, $client_ids){
            $query->where('year', $year-1)->where('week', '<=', 47)->whereIn('client_id', $client_ids);
        })->groupBy('week')->selectRaw('sum(total) as total, week')->get();

That is my weekly query which works perfectly.

$sortedMonthlySalesLastYear = DB::table('invoices')
        ->where('year', $year-1)->whereIn('client_id', $client_ids)
        ->groupBy('month')->selectRaw('sum(total) as total, month')->get();

And this is my monthly query which doesn't work. I know that there is an issue with whereIn clauses in eloquent where they don't accept a big number of elements for some reason, but I'm wondering why one works and not the other one and if there is a solution to it. I also want it to be an object, I've tried using a raw query but it throws an array instead, and I would rather avoid using that. This is the one that worked.

$sortedMonthlySalesLastYear = DB::select( DB::raw("SELECT SUM(total) AS total, month FROM invoices WHERE year = '$lastYear' AND client_id IN ($client_ids_query) GROUP BY month"))

Schema::create('invoices', function (Blueprint $table) {
        $table->id();
        $table->string('month');
        $table->integer('year');
        $table->integer('week');
        $table->integer('client_id')->index()->unsigned();
        $table->integer('product_id')->index()->unsigned();
        $table->integer('quantity');
        $table->float('total');
        $table->double('discount');
    });

This is what my invoices migration looks like, the client relates to the user and that's how I get the arrays.

This is what the monthly query returns:

[2022-05-02 23:40:05] local.INFO: monthly sales:  
[2022-05-02 23:40:05] local.INFO: []  

And this is what the weekly one returns (it's a larger set but this is a sample of what it throws to show its working.)

[2022-05-02 23:42:42] local.INFO: weekly sales: 
[2022-05-02 23:42:42] local.INFO: 
[{"total":536190.4699999997,"week":1}, 
 {"total":568192.6700000003,"week":2}, 
 {"total":1613808.48,"week":3},
 {"total":878447.3600000001,"week":4}...]

An example of a few invoices I'm trying to process is this (there are more than 130K invoices in the database):

I'd appreciate any help and if you have a solution to this, I mostly just prefer to stay using eloquent for the code to look cleaner. Thank you.

I also have to add that the query returns the expected values if I sign in with any other user since the range of clients they have is much smaller.

Answer 1

I figured it out after so long. The only thing I did was implode the client_ids collection and then explode it into an array. No idea why it does accept a big array and not a big collection, and still no idea about the discrepancy between the queries.

$clients = Client::where('user_id', $user_id)->get('id');
$imp = $clients->implode('id', ', ');
$client_ids = explode(', ', $imp);

All queries work with that.