100k rows with() eager loading result in - too many SQL parameters?

What version of Laravel are you using?

@STAUDENMEIR - I'm using Laravel 5.7 with php 7.2 and postgresql 9.3

It looks like PDO stores the number of request parameters in an immutable int, so it cannot be more than 65,535 for some reason.

I don't know if anyone has run into this problem. It looks like the solution could be to split the query in smaller chunks.

What exact version of Laravel 5.7?

5.7.20

It doesn't look like Laravel is the problem but rather it seems to be a limitation of PHP's PDO.

It is PDO-related, but Laravel 5.7.14+ doesn't use bindings anymore when eager loading integer keys: https://github.com/laravel/framework/pull/26434

Can you post the Post and Comment models?

Can you explain what you mean that it doesn't use bindings? How does it eager load it?

I have debugbar installed so I looked at the actual queries executed and this is what it shows

select * from `posts` order by `i_date` desc limit 10 offset 0

select * from `comments` where `comments`.`post_id` in (1, 2, 3, 4, 5, 9, 10, 11, 12, 13) order by `id` desc

Post model

public function comments()
{
     return $this->hasMany('App\Models\Comments', 'post_id', 'id')->orderBy('id', 'desc');
}

Comments model is a just plain Model.

Normally, Laravel use prepared statements with bindings:

Comment::find(1);

select * from `comments` where `id` = ? limit 1 // Bindings: [1]

Since Laravel 5.7.14, eager loading puts integer keys directly in the query:

select * from `comments` where `comments`.`post_id` in (1, 2, 3)

This way, errors like yours shouldn't be possible anymore.

Are you actually getting this error? Or are you asking a theoretical question?

@STAUDENMEIR - I just ran a test and I got the error.

Route::get('test', function () {
        
        ini_set('memory_limit', '2G');

        $posts = \App\Models\Post::select('id')->take(70000)->get();
        $comments = \App\Models\Comments::whereIn('post_id', $posts->pluck('id'))->get();

        return $comments->first();
    });

It gave me this error

Illuminate \ Database \ QueryException (HY000)

"SQLSTATE[HY000]: General error: 7 number of parameters must be between 0 and 65535 (SQL: select * from "comments" where "post_id" in (149519, 142920, 147987, 147045, 148070, 142797, 149327, 142862, 142681, 167789, 142910, 143819, 142077, 145681, 140397, 142351, 141390, 143577, 149932, 141714, 140597, 142972, 149902, 142817, 142542, 149336, 140365, 142613, 142209, 140590, 147231, 142969, 142950, 147842, 141567, 140501, 149357, 141789, 145979, 147125, 143841, 145495, 149812, 141522, 148000, 141616, 140843, 149852, 142980, 143532, 149581, 141434, 149312, 149811, 149214, 149161, 142793, 140529, 147594, 142061, 149179, 143644, 142547, 143881, 140968, 145448, 142661, 142370, 145784, 143733, 142106, 149305, 141896, 143218, 146113, 140342, 149275, 142605, 147943, 147041, 142809, 140268, 140162, 140265, 140300, 140288, 140303, 141860, 140304, 140325, 140354, 140356, 140372, 149320, 140402, 140410, 140412, 140416, 149255, 140422, 140431, 140481, 140492, 140508, 140539, 140556, 140573, 174651, 140577, 140639, 140640, 140668, 145462, 140687, 140697, 140700, 140717, 141722, 140725, 140727, 140742, 140759, 140814, 141637, 140822, 140858, 140862, 149332, 149331, 140873, 149314, 140876, 149291, 149290, 140877, 140887, 140888, 140913, 140914, 174520, 140920, 140923, 140931, 140939, 160881, 140949, 140944...................................

whereIn() still relies on bindings (as it should), but eager loading now uses whereIntegerInRaw() for integer keys.

@STAUDENMEIR - Oh I see, so I shouldn't use whereIn() with more than 65k bindings? But instead use eager loading when possible?

Yes.