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

Posted 2 months ago by MartinZeltin

I noticed that when I eager load the Posts with comments it makes an interesting query using WHERE IN. But SQL has parameter limit of 65535 (at least my old Postgres server does).

So what happens when we try to query for 100k rows and eager load a related table like comments?

  $posts = Post::with('comments')->get()

It will end up with 2 queries like this

select * from posts
select * from comments where comments.post_id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100 ......... 100000)

In which case, wouldn't we get this error? So is this a flaw in Laravel's eloquent?

Failed to execute the SQL statement: SQLSTATE[HY000]: General error: 7 number of parameters must be between 0 and 65535.

Please sign in or create an account to participate in this conversation.