In this episode, we are going to implement the ability to sort our data. But before we do that, we need to get our User model and our AuthorController all up to speed as far as our filtering is concerned. So let's start with our User model, and let's create that scopeFilter method so that we will get the Eloquent query builder. And then we will get our query filter so that we can call the apply method and then pass in the builder there. So that's going to kickstart that process so that then we just need to create our AuthorFilter. And let's do this, let's copy our TicketFilter and let's rename that to AuthorFilter. We, of course, need to change the name of the class, but this is going to give us a lot of functionality that we need. Like, for example, createdAt and updatedAt are going to be the same, so we don't need to touch those. Include is going to be the same. Status, however, is not, but we can reuse this functionality so that if we wanted to filter based upon a set of IDs, then we can do that.

Include is going to be the same. Status, however, is not, but we can reuse this functionality so that if we wanted to filter based upon a set of IDs, then we can do that. We have the functionality, we just need to change the name of the method and the name of the database column that we query on. So we're good there. And then the title is the same functionality for the email, because this way we can filter based upon a partial email. And we also have a name attribute as well. So let's just copy and paste. We'll change the name of that method to name, and then the column is name. So there we go. We have our author filter.

So there we go. We have our author filter. So now we can go to the AuthorController and we can change our index method so that instead of including all of that manually, we will rely upon our author filter to do that for us. So that's here. We will call User and then filter. We will pass in the filters, and then we will paginate the results. So let's do a sanity check and make sure that this is going to work. So we will have our filter. We want to filter on the ID.

So we will have our filter. We want to filter on the ID. And then let's say that we want the users with an ID of 1, 6, and I forget how many users we have. Do we have 15 users? We'll try it and see. We will have the user with an ID of 1, user with an ID of 6, and no user with an ID of 15. So I guess we have just 10 users. So let's change that. So that's 1, 6, and 10. And sure enough, there's the 10th.

So that's 1, 6, and 10. And sure enough, there's the 10th. So we're good as far as our user filter is concerned. So we can close that out. And now let's talk about our sort. So our sort, of course, is going to be provided via the query string. We could have a sort parameter. And in fact, we could do something like we did with the filter so that we could have sort, and then we could specify the column as a key, like for the title. And then we could have the value as being ascending or descending.

and then we could specify the column as a key, like for the title. And then we could have the value as being ascending or descending. And that would work. But, you know, then if we wanted to sort by multiple columns, then we would have to have sort and then like status. And then it's just it gets very verbose very quickly. And I'm not sure I want to do that for something as simple as sorting. Because with sorting, we have two values, ascending, descending. That's it. So maybe we should do something like this,

That's it. So maybe we should do something like this, to where we would have just a single sort query string parameter, and then we could have just a comma-separated value so that we could have title. And if we wanted to also then sort by status, I'm used to saying filter. If we wanted to sort by title and then status, then we can do that, just separate them with a comma, and then that would work. And the default would be ascending sort. Or we could specify a descending sort by putting a minus sign before the column name. And I think that that's fine.

Or we could specify a descending sort by putting a minus sign before the column name. And I think that that's fine. It's nice, simple, clean. Yeah, let's do it. So let's do this inside of our query filter. Because we have all of the plumbing set up already to parse out stuff in the query string. So from a functionality standpoint, it just kind of makes sense to do this here. I'm going to reorder this so that we are in alphabetical order, because I'm that way. And then we will essentially do what we did right for the filter.

because I'm that way. And then we will essentially do what we did right for the filter so that we will have a protected function. We'll call it sort to where we will get the parameter value. And since this can be a comma-separated list of values, let's first of all convert that into an array. So we'll have a variable called sortAttributes to where we will explode on a comma for the value. And then we could iterate over each one of these attributes. And we'll just call that as a sortAttribute.

And then we could iterate over each one of these attributes. And we'll just call that as a sort attribute. And here we need to determine the direction of our sort. And we'll say that the default is going to be an ascending sort. So to check to see if we need a descending sort, we can check the first character in the sort attribute. And if it is a minus sign, then we know that this is a descending sort, so we will need to change the direction to descending. But then we can also go ahead

so we will need to change the direction to descending. But then we can also go ahead and we can normalize our sort attribute here. Because this is going to have a minus sign in front of it, we can take that out by getting the substring of our sort attribute, starting with the position of 1. So that will give us just the column name that we need there. And since we have the direction, all we would need to do is use the builder, order by the sort attribute, and then the direction.

all we would need to do is use the builder, order by the sort attribute, and then the direction. And that should work. So let's go back to Postman and let's sort by title. And we should start with the A's, we do. So it looks like that that's going to work fine. If we say to sort in descending order for our title, yes, that works just fine as well. Let's change that so that we now sort by the status. We should start with the status of A for the active statuses.

Let's change that so that we now sort by the status. We should start with the status of A for the active statuses. But then let's say that's okay. We want to then sort by title descending, in which case, yep, so we are sorting by the status and then title in descending order. So yeah, that's looking great, except for this. What if someone says, okay, I want to sort by whatever? Well, we're going to get an error because there is no whatever column.

Well, we're going to get an error because there is no whatever column inside of our tickets table. So we kind of need to filter out anything that we don't want to sort by so that we could have kind of a privileged list of what is sortable. So inside of our query filter, we could create a sortable, and we could initialize that as an empty array. And then inside of our ticket filter,

and we could initialize that as an empty array. And then inside of our ticket filter, we could then override that to specify the columns that we want to allow to be sortable. So this could be title, and then we could say status as well. And then inside of our query filter, we will need to check to see if our sort attribute is inside of the sortable array. So we'll do it like this. If not in array, then we are just going to continue

So we'll do it like this. If not in array, then we are just going to continue because it's not an attribute that we want to sort by. So we'll just ignore it. But if it is in the array, then of course it is added to the query builder. So we can test this out now. If we submit the same request, this error should go away and we should get our results. That's perfect.

this error should go away and we should get our results. That's perfect. That's exactly what we want. And we can sort by whatever and then status and then a descending title. And we would get kind of the same results as we got before because whatever is being ignored. And that's great, except what about createdAt? Because createdAt is a valid attribute, but it is in camel case.

Because createdAt is a valid attribute, but it is in camel case. And the database is going to expect it to be in the format that it is in the table, created_under_score_at. So let's look at this. If we add createdAt here to our sortable array and then we submit the request, we get that error again. So what if we do something like this to where our sortable array, we can have just the straight normal column names.

to where our sortable array, we can have just the straight normal column names or the attributes. But then if we needed to translate an attribute into an actual database column, we could do something like that, which means that inside of our query filter, we're going to need a little bit more here so that if it is not inside of the sortable array and it does not exist as a key inside of the sortable array,

so that if it is not inside of the sortable array and it does not exist as a key inside of the sortable array, then we will ignore it. So just like before. So if the provided sort attribute is not in the array or if it's not a key in the array, then we continue on, we ignore it. Otherwise, we have a valid attribute that we want to use, but we need to do something like this. Let's get columnName so that we will use our sortable

but we need to do something like this. Let's get column name so that we will use our sortable and try to get the value for the sort attribute key. So in the case of our createdAt, that means that we would be providing the createdAt key to get to the createdAt value. And if you're not looking at the screen, that makes completely and totally nonsense, but that's exactly what it is. However, in the case of something like title or status,

but that's exactly what it is. However, in the case of something like title or status, the value is not going to be there because that's not a key. So we could do something like this so that if this is a key in the sortable array, we will get the value. Otherwise, it's going to be null. But then we can check if the columnName is null, then we are going to set the columnName equal to the sortAttribute

then we are going to set the column name equal to the sort attribute so that in the case of status and title, this is going to set our column name back to those values. Otherwise, it's going to be the value of created_at. And we can go ahead and we could do the updatedAt as well so that that will be in place so that then inside of our query filter, instead of using our sort attribute, we will use the column name.

instead of using our sort attribute, we will use the column name. We already know the direction and that should work. So now we should sort on the createdAt attribute, but this is going to be in descending order. Okay, I mistyped the field. So that is createdAt, not createAt. So let's go back. Sure enough, we now get our data. If we look at the createdAt,

Sure enough, we now get our data. If we look at createdAt, we can see that that was February 4th. That is the latest one. And if we scroll on down, we see February 3rd. Then we are going to see a ton of February 2nd. But if we switch this to be in ascending order, we will start at February 1st. Then if we want to sort by the title, we will see that our first ticket is still from February 1st.

Then if we want to sort by the title, we will see that our first ticket is still from February 1st, but then we have February 2nd. The title starts with A and everything is sorting exactly as it should. So we are good to go as far as our sort capability is concerned. Let's go ahead and go to the author filter and let's copy what we have from the ticket filter as far as our sortable array. And then we will just set this up

as far as our sortable array. And then we will just set this up so that we can sort based upon the name, the email. I guess we would want to sort by the ID. I guess that's something that we would want to do. Or maybe not. Who cares if we don't sort by ID? Definitely name, definitely email, createdAt, updatedAt, everything else is fine. And we know that it's going to work

createdAt, updatedAt, everything else is fine. And we know that it's going to work because, well, we've tested it on our tickets. And the really awesome thing about this is if we go back to our nested controller for the Author Tickets, not only can we include the filter, but we can also include the sort. I don't remember how many active tickets we have. Looks like we have several.

I don't remember how many active tickets we have. Looks like we have several. So let's sort by the title descending and we will see that starts with an L, that starts with an D, that works just fine because it's all built into our query filter.