Optimizing Queries with Eloquent in Laravel
We are going to talk about the importance of optimizing database queries in Laravel and knowing how to do them.
We start from a relationship with the Post and a relationship with the category, that is to say, obviously it is like who says that the categories inherit the relationships that the base category already has, which in this case would be the post category, and what the hell do I want to get to with all this, which is the optimization part of the queries? Here you can see a little how I am going to optimize it, but to know what the hell we have to put here since the issue is mainly in the listing, that is, the Index, which would be the table that we have here. Here it is important to know what the hell we are handling to know what the hell we have to optimize since if, for example, here we are not using the category:
<td>
{{ $p->id }}
</td>
<td>
{{ $p->title }}
</td>
<td>
{{ $p->posted }}
</td>
<td>
{{ $p->category->title }}
</td>
And here we would not be using either the post or the category, therefore it would not be necessary to bring it to the person who bases the relationship. All this depends on what they ask you for the page you are building. In my case, I do want to place the category:
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'];
}Since it is a bit logical but if it is a book, a Laravel book I have to put What is the category in this case I did not put it directly since I am bringing it from the post I did not need it because if not it would be a duality there one of the characteristics that a database must have to avoid redundancy So if I put a category again to the Book it did not make sense because how could I select a different category for example suppose php and Laravel then the post with Laravel and the Book with php it does not make sense there for me then I handle it this way and it is the list that you can see exactly not here but it would be the book one over here here I have the category then suppose it would be a bit like who says that we would do it at the beginning we do not put it here for the queries:
Book List
To build this query, we do the following operation:
$books = Book
::with('post', 'post.category')
->select('id', 'title', 'subtitle', 'date', 'posted', 'post_id')->orderBy($this->sortColumn, $this->sortDirection);Eloquent and Query Builder
To put this together you have two ways, one is using the left join and join and so on, which is a bit like it says here, and the other is using Eloquent methods such as the with that brings us the relationship. Remember that the difference is that here you don't put the with:
$books = Book::select('id', 'title', 'subtitle', 'date', 'posted', 'post_id')->orderBy($this->sortColumn, $this->sortDirection);We left it like this, this will work anyway if you install the debug bar plugin as we do in the course and there you could see this in more detail Ah, as they say, in the first steps it seems that everything is going exactly the same since notice that everything loads more or less at the same speed But if you install the debugbar plugin or directly enable the queries made to the application, you will see that here we have the problem of n +1 in which for each record we are making an additional query to the database since what is happening here Remember that in Laravel it works with the Lazy loading scheme, which means that it will bring the Data when it is requested, that is, here by effect it is not bringing it because we are not placing the with here, therefore when we reference the post here, it will see that Book does not exist and it will be a query to the database And additionally here we would have another problem and that is that here we are also bringing the category:
{{ $b->id }}
{{ $b->title }}
{{ $b->subtitle }}
{{ $b->date->format('d-m-Y') }}
{{ $b->post->category->title }}
{{ $b->posted }}List of index books
So once the post is brought, it would also bring the category later, so it would be something like a 2 by n + 1 problem because there would be two additional queries to the database for each record, which would be a problem, the thing is that here in development it is always difficult to see it because it is a completely local environment, everything works quite fast, but when we go to production and multiple people are connecting to the application, then you will have a lot of problems, so the first thing you have to do here is based on the relationships that we are handling from the list, since the problem is always in the list, especially here we have to do this optimization either by join left join or whatever you want or directly with the with, which would always be recommended here.
First recommendation, use with or joins
So first recommendation What is the next recommendation here? A bit the same but based on the data that you are going to manage from here, here you place a Select. This may not seem like much but still. Remember that with this you are avoiding that from the connection to the database it brings more data that you are not going to use, especially commenting a little on what I indicated to you at the beginning.
As recommended, bring only the columns that you are going to use.
For the post I have a content field for the entire post, for the category I also have a content field for what is a super post and also for Book I also have a content field which is for the HTML content, but, we do NOT use it in the list, therefore, we should NOT bring it.
Remember that we are bringing less data from the database And that is appreciated in the end because you are using less resources and here we return to the same thing that the objective is to optimize our queries understand simply return the minimum and necessary and no more then that is very important here a detail with the Select is that if you for example here do not put the depos ID then basically everything is going to crash it was something that cost me a little at the beginning I did not read the official documentation may say it but it is so long and you will see that it tells you that category is null everyone is null and everything went to hell What is happening here that it cannot bring the post here independently if you are specifying it here with the with or not because it does not have as who says the connector it needs the column a relational to be able to bring its friends that would be the post and the category So even if we are not using this directly here on the side it has to be present here So that is a big detail that you have to keep in mind or else it will give you an error that on the one hand What is the Next problem Okay, as I was telling you, I don't want to bring the content of the post because I'm not going to use it, so how the hell do I limit it here? Here we have a couple of ways:
One is by specifying here the fields you're going to work with. In this case, I want the category ID field, so I can put two points and category ID. With this, we are indicating which fields we want to bring and here you can put the rest, for example, in my case, posted and so on. In case I'm going to use it, I'm not going to use it, but it's to give an example, that is, separate the fields with commas:
Post::with('category:id,url_clean,title')But you will see that I don't know why there are times when I am using both the Select and the With.
Another way is using the Select with the With in the model with Exclusive Fields:
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'));
}
}Rest API
If you can't pass it through here then pass it directly from the relationship as I showed you here then finally everything mentioned also has to be applied to a RestAPI I think it makes perfect sense if for what are the normal queries that we have here to paint a table it makes sense to optimize the queries for a RestAPI I think it is more than obligatory here I also have another question:
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 the left join for no reason but that is how I implemented it and here again with the Select in this case I did not use the Wi because again I am using the l join here Although I go better for this one that is simpler since the one above is when it is authenticated here I am indicating What is the data that I want in this case again it would be the list and I do not need the content for the reason mentioned before it is not usually used So I place the fields with which I am going to work and Well here you can see a little the difference Here I have the quick one this would be based on the specified one I do not have the content only the post with the fields that I have from the relationship That is to say here It does not bring the content either for the reason mentioned here in here if you have to place content that will have to be placed because I am going to pull it from the book detail I do not want another one here all this would appear this then I have to see how to optimize that but well at the moment I do not have it then it would be like this I think it is quite easy to understand here but without getting here to the relationship again the direct one here would be the one Book that also has a content here it is and Well here you can quickly compare this What happens if I remove all this and put get you can see that now I also have the content which is basically a page So if for the other it made sense for a dashboard module for a raspi I think it makes a lot more sense since here it is a little different first because you are placing data that you are not really going to use as is the case of the contempor listing and second that whatever is going to consume this which is usually going to be a mobile device the page This is going to weigh To put it in a colloquial way much more than what we had here initially therefore here it is more than mandatory to also optimize your requests to handle only the data with which you are going to work since again whatever is going to consume this Api is not that it is going to consume one by one it is that you have to wait for the entire page to load which is obviously going to have a greater weight and it would be considerably greater because it has more content Although in the end it does not weigh as much but it is still good to optimize it when it had content to when it did not and apart from this you also add all the extra load that is going to having the database without any other need apart from that you also gain in modularization and organization of your project So yes or yes You always have to optimize your queries So that was the message I wanted to give you without further ado see you in another video
- Andrés Cruz
Develop with Laravel, Django, Flask, CodeIgniter, HTML5, CSS3, MySQL, JavaScript, Vue, Android, iOS, Flutter