DEV Community

Cover image for Avoid The Pain of Repeated Database Queries With Laravel Query Scopes
Kevin Hicks
Kevin Hicks

Posted on • Originally published at kevinhicks.software

Avoid The Pain of Repeated Database Queries With Laravel Query Scopes

As you continue building out Laravel apps, you will notice spots that you need to perform the same queries. If your project is like most projects I've worked on, you will start with just repeating the query code in many places.

This works great until you need to change the query. Because the query is repeated throughout your code, you now need to change it everywhere. And you might forget to change it somewhere 🤦🏻. This is an enormous maintenance headache. I can't even count how many bugs I've encountered (and written myself) from forgetting to update code in multiple places.Luckily there are a few easy solutions to reduce duplicate queries.

  • You can move all database code to stored procedures and only interact with the database through stored procedures.
  • Create repository, service, builder, or other classes for your queries.
  • Add methods to the Eloquent model to perform the queries.

All of these solutions will work and have their pros and cons. One solution that is built into Laravel that works well for this is Eloquent Query Scopes. Query scopes provide some advantages over the other methods I mentioned. Three of the most significant benefits are:

  1. It is a first-class citizen of Laravel and built into how Eloquent works
  2. It can be easier to create smaller, reusable query scopes that can be combined in larger query scopes or one-off queries.
  3. They can be automatically applied to all queries on a model

So what exactly is a query scope?

A query scope is a method that works on a model's query builder to modify the default query that would be run. What makes a query scope different from any other method you would write is that it is built into Laravel's Eloquent and meant to be a set of common constraints you would run instead of the entire query. Here is a typical method you might write to tasks that are not complete yet and assigned to a user.

public function GetActiveTasksForUser(int userId) : Collection
{  
    return Task::where('assigned_to_user_id', $userId)
        ->where('isComplete', false)
        ->get();
}
Enter fullscreen mode Exit fullscreen mode

Now, if you think about the typical queries, you might need for a task. It isn't hard to imagine needing to query both for all tasks for a user and another for their tasks that aren't complete. Let's add a second method to only get the tasks assigned to a user.

public function GetAllTasksForUser(int userId) : Collection
{  
    return Task::where('assigned_to_user_id', $userId)->get();
}
Enter fullscreen mode Exit fullscreen mode

Notice how the first half of this method is the same as the previous one. We are already duplicating code after just two different queries. It should be easy to see how this could get out of control pretty quickly. Let's convert the two constraints (tasks assigned to the user and incomplete tasks) to query scopes to see how that would help these methods.

// In the task model (Task.php)
public function scopeAssignedToUser(Builder $query, int $userId) : void
{
    $query->where('assignedtouser_id', $userId);
}
 
public function scopeIncomplete(Builder $query) : void
{
    $query->where('isComplete', false);
}
Enter fullscreen mode Exit fullscreen mode

The two methods from before can be updated to use the new query scopes and avoid the duplicated query where clause.

public function GetActiveTasksForUser(int userId) : Collection
{  
    return Task::assignedToUser($userId)->incomplete()->get();
}

public function GetAllTasksForUser(int userId) : Collection
{  
    return Task::assignedToUser()->get();
}
Enter fullscreen mode Exit fullscreen mode

The best thing about this approach is that we call simple methods whenever we need a new query that either gets tasks assigned to a specific user or incomplete.

How to create a query scope?

Creating query scopes is pretty similar to creating any method. There are just three main requirements to make the method a query scope.

  1. Local scopes need to be on the model they apply to. Global scopes need to implement Illuminate\Database\Eloquent\Scope; We'll get into local vs. global scopes in a little bit.
  2. Local scopes need to start with scope. For local scopes, everything after the word scope becomes the method name to call to use the scope using camelCasing. Ex: scopeAssignedToUser() is called with assignedToUser. Global scopes don't have any special naming requirements.
  3. The first argument should be an instance of Illuminate\Database\Eloquent\Builder. For global scopes, the second argument should be an instance of Illuminate\Database\Eloquent\Model.

Local scopes can have no additional arguments or can have any arguments after the builder argument. Arguments in a local scope are used to change the query that runs. For example, in scopeAssignedToUser we passed in an $userId argument to change what user the query was checking tasks were assigned to.

But queries don't belong in the model.

Depending on the size and complexity of your app or your coding standards, you might think that query scopes don't belong in the model. It is true that with Eloquent, it can be very easy to make your models bloated and full of logic that doesn't belong in it. If you aren't careful, the model can become a mixture of business logic, persistence layer, and other logic.

Don't write off query scopes just yet. There is an easy way to take advantage of query scopes without bloating the model. Since query scopes are just methods that work on the model's query builder, you can extend the query builder and include your query scopes on the extended class. This puts all query scopes for each model in its own dedicated class.

// In TaskQueryBuilder.php
use Illuminate\Database\Eloquent\Builder;

class TaskQueryBuilder extends Builder
{
    public function assignedToUser(int $userId) : self
    {
        return $this->where('assigned_to_user_id', $userId);
    }
}

// In Task.php
public function newEloquentBuilder($query) : TaskQueryBuilder
{
    return new TaskQueryBuilder($query);
}

// Still can be called the same way as before
Task::assignedToUser($userId)->get();
Enter fullscreen mode Exit fullscreen mode

Local Scopes vs. Global Scopes

The main difference between a local scope and global scope is local scopes have to be called and are meant for repeated database constraints you may not need all the time. Global scopes, on the other hand, will run automatically for every query on a model. They are meant for things you are almost always going to want to run and don't want to forget when querying for that model. For global scopes, think of things like not loading soft deleted records (Laravel's SoftDeletes trait uses global scopes) or checking if a record belongs to a company for multi-tenant apps (apps that serve multiple different companies each with their own dataset).

The scopes we created above were local scopes. The following examples will be global scopes that have a few differences in how they are made and used.

  • They need their own dedicated class and to be registered in the model's booted method.
  • The method names are not prefixed with scope.
  • They can not have arguments that easily change for each query in the same request. Any arguments for global scopes will require some type of global state.
  • They run without calling them explicitly. To avoid running them for a query, the withoutGlobalScope method needs to be used.

Let's create an example Global scope for checking if a record belongs to a company.

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Scope;

class BelongsToCompanyScope implements Scope
{
    protected CompanyResolver $resolver;

    public function __construct(CompanyResolver $resolver)
    {
        $this->resolver = $resolver
    }

    /**
     * Apply the scope to a given Eloquent query builder.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $builder
     * @param  \Illuminate\Database\Eloquent\Model  $model
     *
     * @return void
     */
    public function apply(Builder $builder, Model $model)
    {
        $builder->where('company_id', $this->resolver->getCurrentCompanyId());
    }
}
Enter fullscreen mode Exit fullscreen mode

This example shows how we can use a resolver passed into the global scope to change what company we are loading records for. The resolver could be any class that tracks the current company for the current user. Maybe it is stored in the session, cache, or querying the user's database table.

This is one of the easiest ways to get global scopes to work with arguments that could change based on the current user or session. However, it does have its downsides that we are now introducing some type of global state into the scope, and the model will also have to know about the CompanyResolver too. This can make fixing bugs and changing features in the future harder. For this reason, I'm not a big fan of doing this on larger projects or for anything that doesn't rely on a global user or session state the entire app needs to know about anyways.

To use this BelongsToCompany scope, it needs to be registered in the model class.

// In Task.php
/**
 * The "booted" method of the model.
 *
 * @return void
 */
protected static function booted()
{
    static::addGlobalScope(new BelongsToCompanyScope(new CompanyResolver));
}
Enter fullscreen mode Exit fullscreen mode

Now every time we query for a task, it will automatically add where company_id = $someCompanyId to the query. However, if we don't want the global scope to run, excluding them for specific queries is also easy. For example, maybe we want to get all tasks for all companies. In this case, we need to exclude the BelongsToCompanyScope.

Task::withoutGlobalScope(BelongsToCompanyScope::class);
Enter fullscreen mode Exit fullscreen mode

If we wanted to exclude multiple scopes or all scopes, we could use withoutGlobalScopes. When called with no arguments, it removes all scopes. When called with an array, it will remove all scopes in the array.

Query scopes are just one way to reduce duplicate query code

As mentioned earlier in the article, query scopes are just one way to reduce duplicate code. They might not be the best for every situation or project, but they should be another tool in your belt. It is up to you and your team to decide what patterns and practices you want to follow for your project.

Top comments (0)