Archive Query Goal 0:00Now, it's true that Eloquent is incredibly powerful and flexible. So yes, you can fetch all posts, but you can also add conditions, you could add left joins if you want, you could group the results by. So as long as you have some basic experience working with SQL, you should be able to migrate it over. But still, at the same time, there might be situations where you need a very specific query. Maybe something like this. Well, first, let's go to our blog. Now you'll see this archive section.Well, first, let's go to our blog. Now you'll see this archive section. We can see how exactly would we populate that. Because it sounds like we would need to do a database query to fetch the posts, but then group them and sort them according to each month. How would we do that? This is tough for newcomers. Let's think about it. I'm going to go to the query section in SQL Pro. Here's all the posts we have right now. Build SQL Grouping 0:44I'm going to go to the query section in SQL Pro. Here's all the posts we have right now. Now one thing we could do is we could say select year. So format the created at field as the year from posts and run that. And now we get the year for every single post. But notice the column name. Let's give that a better name. We could say as year or just simply year. And now we have a column name. Okay, so we could do that.And now we have a column name. Okay, so we could do that. But I need the month too. So I could say month created at and call that month 1, 1, 2. So January, January, February. Or we could do month name. Okay. But next, I also want the total number of posts for that month. So ideally, you'll see that I updated this one to be a month from now. So ideally, we'd have two for January and one for February.So ideally, you'll see that I updated this one to be a month from now. So ideally, we'd have two for January and one for February. Okay. So it sounds like we need to get the total count of the records. And we'll call that published. So now if we run that, we're going to get an issue here. Select list contains a non-aggregated column. So yeah, here's the idea. We're trying to get the count, but the count of what? Because if you think about it, we don't want a count of three.We're trying to get the count, but the count of what? Because if you think about it, we don't want a count of three. We want the count grouped according to the year and the month. So again, one for February and two for January. So let's do this. Let's group the results according to the year and the month and give that a refresh. And there we go. So in January 2017, there were two. And in February, there were one. Okay. Translate to Eloquent 2:09And in February, there were one. Okay. So we have our query. But now how would we translate this over to Eloquent? Because we are creating aliases and things like that. Well, let's just give it a shot. We could say app post. And the very first thing, we need to perform a select query. So we could use select. But I know that I'm going to be doing formatting here and aliasing.So we could use select. But I know that I'm going to be doing formatting here and aliasing. So let's do a select raw statement. And that way, I can basically copy some of this over like this. Grab our month name. And then finally, the count that we alias to published, our aggregate. Next, well, we're already selecting from post because we're using our post model. But then we want to group by the year and the month. So we can call the group by method year month. Oh, and whoops, we forgot to fetch the results.So we can call the group by method year month. Oh, and whoops, we forgot to fetch the results. All right, there we go. We have two records that contain our data. And what you could do is even cast that to an array. And now you have your data. You could even do something like throw this into Redis or cache it using one of Laravel's drivers once you learn about that. And that way, you don't have to perform this query over and over. All right.And that way, you don't have to perform this query over and over. All right. So let's grab all of this. Come back to Sublime. And I'm going to put this in post controller slash index for the time being. But later, I'll show you how to do things like view composers or to create a module that can have this query. So we're going to say archives and then make it equal to this query and then format it a little better. And like we've already learned, we could also extract this to a dedicated method.a little better. And like we've already learned, we could also extract this to a dedicated method. So take a look. If we return our archives now, switch back to Chrome, give this a refresh, we have our data. So now we should have everything we need. So let's go to the sidebar that we extracted. And this contains, yeah, the about section and the archives. So give that a refresh. Yeah, it contains this section right here. Render Archives Sidebar 3:53So give that a refresh. Yeah, it contains this section right here. All right. So now archives can be replaced. And we could just say for each archives as month. Then we will spit out a list item with an anchor tag that would presumably filter the data. And then we'll echo out the month name. Or actually, we called it month, right? Yeah, the field is month.Or actually, we called it month, right? Yeah, the field is month. There might be a better. There might be a good term for this. Each one represents the statistics, maybe, stats. I don't know. Anyways, you can work on that on your own. So let's clear out the hardcoded version, like so. Switch back to Chrome, but we get undefined variable archives. So let's see.Switch back to Chrome, but we get undefined variable archives. So let's see. Post controller. We forgot to pass that through. And like I said, this is not going to live here. It's going to live somewhere else when we're done. So now we've passed through the archives and then we filter through them. Yeah. Oh, and actually, you know what? This is an array, right?Oh, and actually, you know what? This is an array, right? It's not an object. So anyways, let's switch back, refresh. And then we have February and January because we have two posts. So for example, if I were to update this, let's have one. This one will be in March. This one will be in February. We should now have three different months. And we do. Order Archives Correctly 5:02We should now have three different months. And we do. But now they're not in the proper order. It should be in descending order, March, February, January. So let's go back to SQL Pro and we can see, yep, that's the order. So it sounds like we want to order by the created at in descending order. But that's not going to work because once again, we have a group by clause. So let's do the minimum, the lowest created at field for the group. And there we go. March, February, and January.And there we go. March, February, and January. And even if you had another one, let's say this will be May of 2016. And then give that a refresh. Exactly what we want. All right. So it sounds like we need to add an order by clause. Let's come back down here. And once again, there is an order by method. But in our case, again, we want to use these functions here.And once again, there is an order by method. But in our case, again, we want to use these functions here. So in these cases, we're going to use order by raw. Okay. So let's come back, give it a refresh. And there we go. March, February, January, May. We should also add the date while we're at it. So come down, we're going to have the month, and then a space, and then the year. Perfect. Filter Posts by Archive 6:02So come down, we're going to have the month, and then a space, and then the year. Perfect. We now have the archives. But now, of course, when we click on one of those, how about May, yeah, it doesn't do anything. It doesn't filter the results. Okay. So let's figure that out, and then we'll call it a day. So let's see. Maybe the anchor tag, following a typical blog, it'll link to the homepage, but thenSo let's see. Maybe the anchor tag, following a typical blog, it'll link to the homepage, but then we're going to pass through these through the query string. So for example, I could say month equals stats month, and then the year will be stats year. Okay. Let's give that a refresh and try. So now May 2016, yeah, we're sending through the query string here. So now maybe it's a simple matter of just checking to see if those exist, and if so, we filter the query accordingly. Maybe something like this.we filter the query accordingly. Maybe something like this. Let's go back to our post controller. Yeah, we're going to have to tweak this. So ideally, here's what we could do. We could say, we could build up the query one at a time. So we could say something like this. If we have request month in the body, then we could say hosts, and there is a where month helper function that will exist on Laravel's builder class. So we could use that.helper function that will exist on Laravel's builder class. So we could use that. We could say where month, where the created at column is equal to the given month. So maybe save that, and if that's not null, filter it down. Next, we could do the exact same thing for the year. But there is actually one thing. This where month, it's not expecting May. It's going to expect a number. So for example, if I hard code this in, five for May, and then we'll just say hosts equals posts and then give me the results.So for example, if I hard code this in, five for May, and then we'll just say hosts equals posts and then give me the results. And then, whoops, we forgot to update this. There's a where year method as well. So anyways, if we come back to Chrome and give this a refresh, sure enough, we get exactly one record that was released in May. And if we go to March, well, once again, we're hard coding that. So we do need to somehow convert, in this case, March to three or May to five. Now, we can do this with PHP state time, or I'm really comfortable with carbon at this point that Laravel includes out of the box.Now, we can do this with PHP state time, or I'm really comfortable with carbon at this point that Laravel includes out of the box. And you've already learned a little bit about it because we learned that, for example, the created at and updated at fields are instances of carbon, which is cool. So you already have some awareness of it. We could say carbon parse the month and then give me the month name. Let me show you a quick example of that. Carbon carbon parse May, and that'll give us a carbon instance. But then I could say, well, give me the, I'm sorry, give me the month, which is five, give me the year, it's 2017, and the day is the 20th.But then I could say, well, give me the, I'm sorry, give me the month, which is five, give me the year, it's 2017, and the day is the 20th. Okay, great. So we're just going to convert that into the month number. So now if we come back and give it a refresh, we can see only the posts that were published in March. Let's make sure that works. Let's change, how about this one, and this will be on March 19th. Okay, refresh, and now we see two records, one in January, and then one in May 2016. Okay, so that works, and it's simple.Okay, refresh, and now we see two records, one in January, and then one in May 2016. Okay, so that works, and it's simple. What you may want to do, this can get kind of messy. You can see it starts to feel a little gross. For my experiences, a good metric is to let the grossness guide you towards refactoring. Rather than maybe following specific patterns you've read about, you keep it as simple as you can until it starts to feel a little gross to you. So at that point, you could refactor. Ideally, it might be nice if I could say, I do want them ordered in the correct way, but I also want to filter them, and maybe I just give the request. Refactor with Query Scope 9:32Ideally, it might be nice if I could say, I do want them ordered in the correct way, but I also want to filter them, and maybe I just give the request. So I want to filter the month and the year, and now I'm going to let a query scope be responsible for handling that, and then give me the results. You could then put those on their own line, and I think that looks pretty clean. So let's make that work. I will visit my post model. You've already learned a bit about query scopes, so we know that we give it a name of scope, and then the name of what we want to call, in our case, filter, that will accept the current query, as well as any data that you pass through to it.and then the name of what we want to call, in our case, filter, that will accept the current query, as well as any data that you pass through to it. These will be our filters. We could maybe do this dynamically and be fancy about it, but also, if we just have two here, it's simple enough to just paste this in as so. Okay, so now let's take a look. If month equals request month, nope, we don't want that. We're going to say filters month, and then this one will be the year. Next, I need to import carbon, like so. Okay, so when we call filter, we give it a list of filters, and we check to see, well,Next, I need to import carbon, like so. Okay, so when we call filter, we give it a list of filters, and we check to see, well, is there a month in that filter? If so, add a WHERE clause. Next, if a year was provided, then we will, once again, filter according to the year. That looks good to me. So now, think about it. All of this junk can be deleted, and of course, this later, we would even refactor. I'm going to leave that for a dedicated lesson on Vue composers, so we'll say this is temporary for now, but we're focused on this.I'm going to leave that for a dedicated lesson on Vue composers, so we'll say this is temporary for now, but we're focused on this. Very, very clean, I think. So let's come back and give that a refresh, and I'm still going to get the exact same thing. So let's try it out. All posts? Good. The ones in 2016, of May, or January, or March? It works, and it's just not that hard once you understand the basics.The ones in 2016, of May, or January, or March? It works, and it's just not that hard once you understand the basics.
