Speed Up Your Laravel Queries with whereIntegerInRaw()

If you've been using Laravel for a while, you're likely familiar with the whereIn() method for filtering records by a list of values. It's clean, expressive, and gets the job done. But did you know that when working with large arrays of integers, there's a significantly faster alternative?

In this article, we’ll uncover the performance benefits of using whereIntegerInRaw() over whereIn(), why it works, and when it's appropriate to make the switch. We’ll also explore other related tips and caveats you should be aware of.

🧠 The Common Pattern: whereIn()

Here’s what most of us typically write:

Product::whereIn('id', range(1, 500))->get();

This will generate a parameterized SQL query that looks something like this:

select * from products where id in (?, ?, ?, ..., ?)

Laravel binds each value as a separate parameter — safe and clear, but as the list grows, this can impact performance due to the overhead of binding hundreds (or thousands) of values.

⚡ The Faster Alternative: whereIntegerInRaw()

Product::whereIntegerInRaw('id', range(1, 500))->get();

This method generates a raw SQL query like:

select * from products where id in (1, 2, 3, ..., 500)

By skipping the parameter binding, Laravel allows the database engine to optimize the query more efficiently, resulting in a faster execution, especially on large datasets.

✅ When to Use whereIntegerInRaw()

Use it only when:

• You're filtering using pure integer values (e.g., id, user_id, etc.).
• The list size is relatively large (100+ values).
• The values come from a trusted source (like range(), pre-validated input, or internal logic).

⚠️ Important Considerations

Before you start replacing every whereIn() with whereIntegerInRaw(), consider the following:

1. Security

Raw SQL always comes with injection risk. Even though whereIntegerInRaw() does internally cast the values to integers, you should never use it with untrusted input (e.g., raw user-submitted arrays).

Bad:

Product::whereIntegerInRaw('id', request()->input('ids'))->get(); // dangerous

Good (after sanitization):

$ids = collect(request()->input('ids'))->map(fn($id) => (int) $id)->all();
Product::whereIntegerInRaw('id', $ids)->get();

2. Small Lists Are Fine with whereIn()

If you're only checking against a few values (e.g., less than 50), the performance difference is negligible. Stick with whereIn() unless profiling shows otherwise.

3. Compatibility

This method is available starting from Laravel 8.25.0. Make sure your Laravel version supports it before using it in production.

4. Readability

Some developers might find whereIntegerInRaw() less readable, especially when quickly scanning code. You may want to add a comment indicating the reason you're using it for clarity.

// using whereIntegerInRaw for better performance on large ID list
Product::whereIntegerInRaw('id', $productIds)->get();

🧪 Benchmark Example

Let’s say you're retrieving 1,000 products by ID:

$ids = range(1, 1000);

• whereIn() may take 120ms to complete the query.
• whereIntegerInRaw() might reduce that to 50ms or even less, depending on the database and query plan.

This difference scales dramatically in high-concurrency environments or API endpoints that handle batch requests.

🛠️ Bonus Tips for Optimizing Queries with Large IN Clauses

• Use pagination or chunking if the list is extremely large (e.g., 10,000+ items).
• Consider denormalized flags or intermediate tables for repeated lookups.
• Add proper indexes (especially on id, user_id, etc.) — IN queries benefit from indexes significantly.

🧾 Summary

✨ Finally

Laravel continues to evolve, and whereIntegerInRaw() is a great example of how you can write expressive code without sacrificing performance.

If you're filtering by large sets of integer IDs — say, fetching related records, logs, or bulk resources — using whereIntegerInRaw() can offer noticeable speed boosts with very little change to your codebase.

Just remember: with great power comes great responsibility. Use it wisely, validate your input, and test your queries with real-world data.

Support Us