Pass a Query Builder to "whereIn" to Reduce Your DB Queries

Introduction

I recently learnt about a cool feature in Laravel that allows you to pass a query builder instance (Illuminate\Database\Query\Builder) directly to the whereIn method of another query builder to reduce the number of separate database queries executed.

So I thought I'd put together a quick example to show you how it works.

Before we get started though, it's important to note that I'm by no means an expert in databases and SQL. Although my hunch is that using the approach I'll show you will result in better performance, I haven't done any benchmarks to check this. I just want to highlight that this approach is possible. So please take this as a suggestion of a way to do things rather than a definitive answer as to the best way to do it. Remember to always test and benchmark your code to see what works best for your specific use case.

Passing a Query Builder to "whereIn"

Let's take some example code which is simplified purely for the sake of this example. In a real-life project, the initial query would be much more complex. We'll assume we have an App\Models\Article model and an App\Models\User model. We want to fetch some articles and get the users who wrote those articles.

The code may look something like this:

use App\Models\Article;
use App\Models\User;

$userIds = Article::query()
    ->limit(3)
    ->pluck('user_id');

$users = User::query()
    ->whereIn('id', $userIds)
    ->get();

Running this code would result in the following SQL queries being executed:

SELECT `user_id`
FROM `articles`
LIMIT 3

SELECT *
FROM `users`
WHERE `id` IN (1, 2, 3)

In the queries above, we can see that the first query fetches the user_id from the articles table, and then the second query fetches the users based on those IDs.

We can update our PHP code so that a single query is executed rather than two separate ones. To do this, rather than using pluck to execute the first query, we can use the select method which keeps the Illuminate\Database\Query\Builder instance without actually executing the query. We can then pass this object directly to the whereIn method:

use App\Models\Article;
use App\Models\User;

$userIdsQuery = Article::query()
    ->limit(3)
    ->select('user_id');

$users = User::query()
    ->whereIn('id', $userIdsQuery)
    ->get();

Although this code looks very similar to the previous example, using this approach will only generate and execute a single SQL query:

SELECT *
FROM `users`
WHERE `id` IN (
    SELECT `user_id`
    FROM `articles`
    LIMIT 3
)

As we can see in the SQL query, we're now using a subquery to fetch the user IDs directly from the articles table, rather than needing to fetch them first in a separate query.

Conclusion

In this Quickfire article, we've looked at how you can pass an instance of Illuminate\Database\Query\Builder directly to the whereIn method in Laravel. This allows you to reduce the number of queries executed and can potentially improve the performance of your application.

If you enjoyed reading this post, you might be interested in checking out my 220+ page ebook "Battle Ready Laravel" which covers similar topics in more depth.

Or, you might want to check out my other 440+ page ebook "Consuming APIs in Laravel" which teaches you how to use Laravel to consume APIs from other services.

If you're interested in getting updated each time I publish a new post, feel free to sign up for my newsletter.

Keep on building awesome stuff! ?