Using The Laravel Builder Object For Flexible Querying

In this post, I’ll explain how we can use Laravel’s Builder Object to improve code reusability when creating a series of complex queries.

When retrieving information from the database in Laravel, it’s easy to get started with something like:

$users = User::all(); 

Let’s add some complexity. What if you wanted to create a user dashboard and display two different groups of users displayed on the page, users with subscriptions and users with deleted subscriptions. We could do something like this.

$subscribed = User::join('subscriptions')
    ->on('subscriptions.user_id', '=', 'users.id');

$churned = User::join('subscriptions')
    ->on('subscriptions.user_id', '=', 'users.id')
    ->whereNotNull('subscriptions.deleted_at');

As the scope of such a user dashboard expands, the queries can begin to get more complex and code duplication skyrockets. For example, we have to join on subscriptions in both of the queries above.

What if we wanted to join across multiple tables with multiple filter combinations? For example, we could end up with two queries like this side by side:

if ($filter === 'thisMonth') {
    $users = User::join('memberships')->on('memberships.user_id', '=', 'users.id')
        ->whereIn('memberships.customerid', $customerIds)
        ->join('subscriptions')->on('subscriptions.user_id', '=', 'users.id')
        ->where('subscriptions.type', 'ANNUAL')
        ->whereNull('subscriptions.deleted_at')
        ->where('subscriptions.created_at', '=', Carbon::now()->startOfMonth())
        ->get();
} elseif ($filter === 'thisWeek') {
    $users = User::join('memberships')->on('memberships.user_id', '=', 'users.id')
        ->whereIn('memberships.customerid', $customerIds)
        ->join('subscriptions')->on('subscriptions.user_id', '=', 'users.id')
        ->where('subscriptions.type', 'ANNUAL')
        ->whereNull('subscriptions.deleted_at')
        ->where('subscriptions.created_at', '=', Carbon::now()->startOfWeek())
        ->get();
}

Clearly, there’s a lot of code duplication here that can be improved. In this use case, it’s helpful to use Eloquent’s Conditional Clauses to scope your query parameters as needed.

$query = User::join('memberships')->on('memberships.user_id', '=', 'users.id')
    ->whereIn('memberships.customerid', $customerIds)
    ->join('subscriptions')->on('subscriptions.user_id', '=', 'users.id')
    ->where('subscriptions.type', 'ANNUAL')
    ->whereNull('subscriptions.deleted_at');

$query->when($filter === 'thisMonth', function ($query) {
    return  $query->where('subscriptions.created_at', '=', Carbon::now()->startOfMonth());
})->when($filter === 'thisWeek', function ($query)  {
    return  $query->where('subscriptions.created_at', '=', Carbon::now()->startOfWeek());
})->get();

In more complicated use cases where you might want to separate out different joins, you can utilize Laravel’s Builder object to construct a base query that an entire subset of queries reads off of, then customize it as needed. For example, we could refactor the original snippet to read as follows.

$query = User::join('memberships')->on('memberships.user_id', '=', 'users.id')
    ->whereIn('memberships.customerid', $customerIds)
    ->join('subscriptions')->on('subscriptions.user_id', '=', 'users.id')
    ->where('subscriptions.type', 'ANNUAL')
    ->whereNull('subscriptions.deleted_at');

if ($filter === 'thisMonth') {
    $query = $query->where('subscriptions.created_at', '=', Carbon::now()->startOfMonth());
} elseif ($filter === 'thisWeek') {
    $query = $query->where('subscriptions.created_at', '=', Carbon::now()->startOfWeek());
}

$query->get();

There are multiple approaches to remove this duplicated code. Using the builder object is particularly helpful if you want to separate out the creation of the query from the actual retrieving of the results. Using conditional clauses is best if you want to apply various criteria that can add on to each other.

Another benefit is the ease of joining and scoping the joins. Without these patterns, I often found myself thinking that the query structure was too complicated, so I would run multiple queries to the database to retrieve data points. Thus, using the builder object has enabled me to greatly reduce the number of queries run against the database and the load of each individual query.

One downside to this approach is that the builder can be difficult to debug. If you dump and die, it spits out a huge object with all of the database bindings.

dd($query);

However, you can circumvent this issue by running the dump and die with the bindings and running the toSQL() method, then reading the raw SQL to see if the query matches your expectations.

dd(str_replace_array('?', $query->getBindings(), $query->toSql()));

Ultimately, this type of approach definitely does not fit all use cases. However, if your project requires a wide variety of queries based on the same base combination, you may find it beneficial for database performance, code reuse, and cross-team collaboration.