In this lesson, we're going to look at how we can minimize our memory usage by only selecting the data we actually need from the database. Now, imagine you're building a blog and your blog in an archive page like this. A page that lists all your articles may be organized by the year that they were posted. A page like this is pretty straightforward to implement. Let's have a look at how I did it with this demo app. We have a PostController which loads all the posts from the database. It eagerLoads the authors for those posts and orders them by the published_at date. From there, it groups them by the published_at year and returns them to our post Blade view. The view iterates through each year, outputting the year as a title,

From there, it groups them by the published at year and returns them to our post Blade view. The view iterates through each year, outputting the year as a title, and then iterates through each post, displaying a link for the post with the title, as well as displaying the published at date and the author name. This implementation seems reasonable enough, but let's take a closer look at the Laravel debug bar. Let's start by looking at the queries tab here. We can see that we're selecting all the posts and we're selecting all the authors for those posts. This seems totally fine.

and we're selecting all the authors for those posts. This seems totally fine. We also have our models tab here, which is grabbing 100 posts, which makes sense because that's what I seeded this database with, as well as 20 authors for those posts. Again, nothing to be concerned about. However, if we look at our memory usage here, this is kind of concerning. We're at 19.8 megabytes. I know for a fact that Laravel on my system uses roughly four megabytes worth of memory. Let me just show you for a second.

I know for a fact that Laravel on my system uses roughly four megabytes worth of memory. Let me just show you for a second. If we go back to our PostsController and we just return Hello World here and hit refresh, you can see there are 3.7 megabytes of memory. So to be close to 20 megabytes of memory, that's quite high. What's causing it to go that high? Well, if you think about it, if we go back to the queries tab here, you can see that we're selecting all the columns from our post table. So even though on this page, we only need the post title, the date and the author, we're still getting all the columns back from the database,

So even though on this page, we only need the post title, the date and the author, we're still getting all the columns back from the database, which includes the article content, the body. And the body of the article is quite large. And that's the reason why this page is using so much memory. So let's update our query to select only the columns that we actually need. We'll go to our PostController and we'll start by selecting the ID. And the reason why I select the ID is because Eloquent expects this primary key to be there. So it's always good to just include it. We're also going to need the title.

So it's always good to just include it. We're also going to need the title. We're going to need the slug for the URL. We're going to need the publishedAt date. And finally, we're going to need the authorId so we can eager load the authors. OK, let's refresh the page. We're at 19.8 megabytes and we're going to hit refresh here. And now we're down to 4.35. That's much, much better. Let me show you one more little trick.

That's much, much better. Let me show you one more little trick. You can use this same technique with your relationships. So it's entirely possible that for your blog, you have additional author information, maybe a bio or social media links or whatever. So in the same way, we don't need all the columns for the Posts. We really don't need all this extra information for the Authors either. Really, all we need for this page is the author name. As it turns out, the with method here allows you to provide a callback where you can actually modify the query.

As it turns out, the with method here allows you to provide a callback where you can actually modify the query. So let's do that. We'll create a function that takes the query and then we'll just call select on the query, requesting the ID and the name, just like the post query. So let's refresh the page here now. And if we look here, you can see we're selecting * from users. That's our authors. So if we hit reload here, you can see that we're now selecting ID and name from users. We can actually clean this up even a little bit more.

So if we hit reload here, you can see that we're now selecting id and name from users. We can actually clean this up even a little bit more. Laravel actually provides a shortcut for this. So let's go back to the way it was before, where it was just eager loading the author as a string. And instead of using the callback to modify the query, this time just put a colon and id and name. And if we hit refresh here, keeping an eye on this query here, you can see it's identical. So this is a nice little shorthand just to avoid having to use that callback.

on pages that interact with a lot of database records, such as index pages, export functions, and stuff like that. The trick really is to just keep an eye on your memory usage. And if you see it climbing like we did in this example, consider this technique to trim down the amount of data that you're pulling back from your database.