If you work with collections of models with relations in Laravel you may notice, that each relation will be loaded separately by a dedicated SQL query. It can make a heavy load on your database and increase query handling time.
In this article we will explain how to optimize this moment using the with() method for Eloquent query builder and collections.
Table of Contents
Prepare Code and Data
I assume that you have Laravel installed. If it is not – see this article. Let’s look at the example in more detail. Imagine that you have a model Article, that has relations comments() and author(). Let’s create these models and the required migrations. Start with migrations:
php artisan make:migration CreateAuthorsTable
return new class extends Migration {
public function up()
{
Schema::create("authors", function (Blueprint $table) {
$table->id();
$table->string("name");
$table->timestamps();
});
}
public function down()
{
Schema::dropIfExists("authors");
}
};
php artisan make:migration CreateArticlesTable
return new class extends Migration {
public function up()
{
Schema::create("articles", function (Blueprint $table) {
$table->id();
$table->string("title");
$table->text("content");
$table
->foreignId("author_id")
->references("id")
->on("authors");
$table->timestamps();
});
}
public function down()
{
Schema::dropIfExists("articles");
}
};
php artisan make:migration CreateCommentsTable
return new class extends Migration {
public function up()
{
Schema::create("comments", function (Blueprint $table) {
$table->id();
$table
->foreignId("article_id")
->references("id")
->on("articles")
->cascadeOnDelete();
$table->string("text");
$table->timestamps();
});
}
public function down()
{
Schema::dropIfExists("comments");
}
};
Then, create models. Run this command to create the Author model:
php artisan make:model Author
And paste this code:
class Author extends Model
{
use HasFactory;
public function article(): HasOne
{
return $this->hasOne(Article::class, "article_id", "id");
}
}
Then, create the Article model in the same way:
php artisan make:model Article
class Article extends Model
{
use HasFactory;
public function author(): BelongsTo
{
return $this->belongsTo(Author::class, "author_id", "id");
}
public function comments(): HasMany
{
return $this->hasMany(Comment::class, "article_id", "id");
}
}
And Comment model:
php artisan make:model Comment
class Comment extends Model
{
use HasFactory;
public function article(): BelongsTo
{
return $this->belongsTo(Article::class, "article_id", "id");
}
}
Next, let’s create factories for these models in the database/factories directory to fill the database with dummy data. First, create factory for the Author model:
class AuthorFactory extends Factory
{
public function definition()
{
return [
"name" => $this->faker->name(),
];
}
}
You can use built-in faker to generate random names and texts. Then, create Article factory:
class ArticleFactory extends Factory
{
public function definition()
{
return [
"title" => $this->faker->text(100),
"content" => $this->faker->text(200),
];
}
}
And Comment factory:
class CommentFactory extends Factory
{
public function definition(): array
{
return [
"text" => $this->faker->text(200),
];
}
}
After this, create a command that will seed your database with dummy data:
php artisan make:command CreateDummyArticlesCommand
And paste this code to the handle() method:
DB::transaction(function () {
$author = Author::factory();
Article::factory()
->count(10)
->for($author, "author")
->has(Comment::factory()->count(10), "comments")
->create();
});
Don’t forget to change the $signature field value to articles:dummy:create. Then, you can run this command:
php artisan articles:dummy:create
Next, create the ArticleResource class, which will be used to display information about articles:
php artisan make:resource ArticleResource
And paste this code into the toArray() method:
return [
"id" => $this->resource->id,
"title" => $this->resource->title,
"content" => $this->resource->content,
"author" => $this->resource->author->name,
"comments" => $this->resource->comments->count(),
"created_at" => $this->resource->created_at,
];
In this resource, I want to display a title, content, author name and comment count for each article. And the last thing – create a controller or closure that will fetch articles from the database and transfer them to the resource:
$articles = Article::query()->paginate(10);
return ArticleResource::collection($articles);
This code fetches ten articles from the database. That’s it. All code is ready for testing. Run the local server:
php artisan serve
Execute the request and ensure that you can get articles:
Now, let’s have look how to use with Eloquent in Laravel.
How to Optimize Database Queries in Laravel
If you run this code and check database queries in Telescope, you will se that it is very slow. Eloquent makes dedicated requests fetch the author and comments for each article. If you display ten articles, Laravel perform twenty requests to the database.
You can optimize this behavior. First, let’s look at the with() method.
1. Using with() Method
The with() method allows you to load a specific relation for all models in the collection by one query to the database. For example, you can load all authors for ten articles by one request. Just change article fetching code to this:
$articles = Article::query()
->with("author")
->paginate(10);
After this, you can rerun this request and view information in Telescope. The situation goes better, now all authors are fetched by one request:
If you want to ensure that all relations are loaded eagerly using the with() method, you can use callback whenLoaded() in the resource. In this case the author’s name will be inserted only if the author relation is already loaded:
return [
"id" => $this->resource->id,
"title" => $this->resource->title,
"content" => $this->resource->content,
"author" => $this->whenLoaded("author", function () {
return $this->resource->author->name;
}),
"comments" => $this->resource->comments->count(),
"created_at" => $this->resource->created_at,
];
But you should also deal with the comment counter.
2. Using withCount() Method
All relations in Laravel have counters, which allow getting the total number of records in the database for each relation. The counters are available by magic property $relation_name_count. For example, to get the number of comments for an article you can use the magic property $comments_count. But this counter should be preloaded using the withCount() method. Change article fetching code to this:
$articles = Article::query()
->with("author")
->withCount("comments")
->paginate(10);
After this, you can change the resource code to use this property:
return [
"id" => $this->resource->id,
"title" => $this->resource->title,
"content" => $this->resource->content,
"author" => $this->whenLoaded("author", function () {
return $this->resource->author->name;
}),
"comments" => $this->resource->comments_count,
"created_at" => $this->resource->created_at,
];
Now, you can make a request and view info in Telescope. The number of comments for each article will be fetched by one request:
Now, we have only three queries to the database. There are a few other preloaders: withAvg(), withMax(), withMin(), withSum(), etc.
3. Using Limit and with() Method
Imagine, that you want to display not only number of comments for each article. You also want to display two recent comments. If you use with() method and limit(2) for the comments relation, Eloquent will load only two comments for the first article. You can fix it using the staudenmeir/eloquent-eager-limit package. Install the package using the composer:
composer require staudenmeir/eloquent-eager-limit
After this, add Staudenmeir\EloquentEagerLimit\HasEagerLimit trait to both models. In this case, Article and Comment:
class Comment extends Model
{
use HasFactory;
use HasEagerLimit;
//.....
}
class Article extends Model
{
use HasFactory;
use HasEagerLimit;
//...
}
Then, you can add a limit directly in the relation method or in the with() as a closure. For example:
$articles = Article::query()
->with("author")
->with([
"comments" => function (Builder $query) {
$query->limit(2);
},
])
->withCount("comments")
->paginate(10);
After this, modify the resource to display fetched comments:
return [
"id" => $this->resource->id,
"title" => $this->resource->title,
"content" => $this->resource->content,
"author" => $this->whenLoaded("author", function () {
return $this->resource->author->name;
}),
"comments" => $this->resource->comments_count,
"latest_comments" => $this->resource->comments->map(function (
Comment $comment
) {
return [
"text" => $comment->text,
"created_at" => $comment->created_at,
];
}),
"created_at" => $this->resource->created_at,
];
Next, you can perform the request and ensure that all comments have been fetched using one SQL query:
Wrapping Up
In this article, we have explained how to use with() in Laravel to optimize database queries. The provided example is very simple, so you might see no performance improvements. But when things become more complex this optimization may be very helpful.
