Optimizing Queries with Eloquent in Laravel

▶

We're going to talk about the importance of optimizing database queries in Laravel and how to do it efficiently. Now that we know how to perform transactional operations, it's important to learn how to perform efficient queries.

Model Relationships

We start from a relationship between Post and Category. That is, Posts inherit the category that corresponds to them. This is important to understand what data we need to load and what we should optimize in our queries, especially in listings (for example, in the index method).

If you are not using the category in your listing table, there is no need to fetch it from the database. For example:

<td>
   {{ $p->id }}
</td>
<td>
   {{ $p->title }}
</td>
<td>
   {{ $p->posted }}
</td>
<td>
   {{ $p->category->title }}
</td>

If you don't use $p->category->title, you don't need to load the relationship, avoiding unnecessary queries.

Models and Relationships

class Book extends Model
{
   use HasFactory;

   protected $fillable = ['title', 'subtitle', 'date', 'url_clean', 'description', 'content', 'image', 'path', 'page', 'posted', 'price', 'price_offers', 'post_id', 'user_id'];

   public function post()
   {
       return $this->belongsTo(Post::class)
           ->select(array('id', 'url_clean', 'title', 'category_id'));
   }
}

class Post extends Model
{
   protected $fillable = ['title', 'url_clean', 'content', 'category_id', 'posted', 'description', 'final_content', 'aux_content', 'web_content', 'image', 'path', 'date', 'type', 'post_id_language', 'language'];
  
   public function category()
   {
       return $this->belongsTo(Category::class)
       ->select(array('id', 'url_clean', 'title'));
   }
}

class Category extends Model
{
   protected $fillable = ['title', 'url_clean', 'image', 'body', 'body_en'];
}

In this case, the Book's category is not assigned directly because it is already fetched through the Post. This avoids redundancy in the database and maintains data integrity.

Optimized Queries

To build this query, we perform the following operation:

$books = Book::with('post', 'post.category')
    ->select('id', 'title', 'subtitle', 'date', 'posted', 'post_id')
    ->orderBy($this->sortColumn, $this->sortDirection);

Eloquent vs Query Builder

• Eloquent (with): fetches the defined relationships, avoiding the N+1 queries problem.
• Query Builder (join, leftJoin): also allows optimization through joins, although the syntax is different.

If we don't use with:

$books = Book::select('id', 'title', 'subtitle', 'date', 'posted', 'post_id')->orderBy($this->sortColumn, $this->sortDirection);

It works at first glance, but when enabling Debugbar or logging queries, we will see that each record causes additional queries when accessing post and category. This is the N+1 problem, which can affect performance in production.

{{ $b->id }}
{{ $b->title }}
{{ $b->subtitle }}
{{ $b->date->format('d-m-Y') }}
{{ $b->post->category->title }}
{{ $b->posted }}

Therefore, once the post is fetched, the category would also be fetched, which generates a 2 times N+1 problem, since two additional queries are performed on the database for each record. This can be a serious issue.

In development, it is often hard to notice because the local environment is fast, but when moving to production, when multiple users are connected to the application, performance problems do arise.

That's why the first thing we must do is optimize queries based on the relationships we are managing, especially in listings, where this problem is more common. Optimization can be done using join, left join, or preferably, using with, which is the recommended way in Laravel.

Optimization Recommendations

1. Use with or joins

Whenever you work with relationships and listings, fetch only the necessary data from the database. For example:

Post::with('category:id,url_clean,title');

Or in the model, limiting the fields of the relationship:

public function category()
{
   return $this->belongsTo(Category::class)
       ->select(['id', 'url_clean', 'title']);
}

2. Select only the necessary columns

Do not fetch large fields like content if you are not going to use them in the listing:

$books = Book::select(
   'books.title', 'books.subtitle', 'books.date', 
   'books.url_clean', 'books.description', 'books.image', 
   'books.path', 'books.page', 'books.posted', 
   'books.price', 'books.price_offers', 'books.post_id'
)->get();

This reduces the amount of data transferred and improves performance.

3. Avoid errors with select and relationships

These recommendations also apply to REST APIs, where it is critical:

• Fetch only the data that the client needs.
• Avoid loading unnecessary heavy content.
• Reduce the number of queries to improve speed and resource consumption.

Example with leftJoin:

Book::select(
   'books.title', 'books.subtitle', 'books.date', 'books.url_clean', 
   'books.description', 'books.image', 'books.path', 'books.page', 
   'books.posted', 'books.price', 'books.price_offers', 'books.post_id',
   DB::raw('DATE_FORMAT(file_payments.created_at, "%d-%m-%Y %H:%i") as date_buyed'),
   'file_payments.payments'
)
->leftJoin('file_payments', function ($leftJoin) use ($user) {
   $leftJoin
       ->on('books.id', 'file_payments.file_paymentable_id')
       ->where("file_paymentable_type", Book::class)
       ->where('file_payments.user_id', $user->id)
       ->where('file_payments.unenroll');
})
->where('posted', 'yes')
->get();

 Rest API and Query Optimization

If you cannot pass the limited fields directly from the query, then you can do it from the relationship, as I showed you before. Everything we mentioned about query optimization also applies to a Rest API, and it makes a lot of sense, especially when dealing with data listings.

For example, a query to the books could be:

$books = Book::select(
    'books.title',
    'books.subtitle',
    'books.date',
    'books.url_clean',
    'books.description',
    'books.image',
    'books.path',
    'books.page',
    'books.posted',
    'books.price',
    'books.price_offers',
    'books.post_id',
    DB::raw('DATE_FORMAT(file_payments.created_at, "%d-%m-%Y %H:%i") as date_buyed'),
    'file_payments.payments'
)->leftJoin('file_payments', function ($leftJoin) use ($user) {
    $leftJoin
        ->on('books.id', 'file_payments.file_paymentable_id')
        ->where("file_paymentable_type", Book::class)
        ->where('file_payments.user_id', $user->id)
        ->where('file_payments.unenroll');
})->where('posted', 'yes')
->get();

In this case, I am using leftJoin to fetch additional data, and then I specify with select only the fields that I really need. I didn't use with because I am handling the joins directly, and the idea is to fetch only the necessary data for the listing, without including heavy fields like content, which will not be used here.

Differences and Considerations

• When we do a listing on the dashboard or for a Rest API, we don't need to fetch full content fields (content) that would only be used in the book's detail view. This reduces the load on the database and optimizes the API response.
• If we need to show full content, only then do we include the content field, for example, in the book's detail view.
• On mobile devices, loading less data is crucial because it reduces the page weight and improves the user experience.

Conclusion

• Always optimize your queries, especially in listings and APIs.
• Use with or joins to reduce the N+1 problem.
• Fetch only the columns you are going to use.
• Avoid redundancy in your models and relationships.
• This improves the performance, modularity, and organization of your project.

Now that we've seen how to perform so many database operations, let's learn about a tool that will allow us to debug Laravel using a toolbar.