In this lesson, we're going to learn how to order records using custom algorithms. To illustrate this, I've set up this page which lists feature requests, including the feature title, status, and activity. The activity column includes both a vote count as well as a comment count. And if we have a look at our FeaturesController, we can see that we're loading all the features from the database, getting the vote and comment counts, ordering them to get the latest ones first, and paginating the results. In this lesson, we're going to add column sorting to all three columns on this page. I've already set up the heading links in the feature.blade.php view.

In this lesson, we're going to add column sorting to all three columns on this page. I've already set up the heading links in the feature blade view. And when you click on a heading, it sorts ascending by default. And if you click it again, it sorts descending. These links include both a sort column name and a sort direction in the query string. Okay, let's start by adding a when call to our query. This will check to see if a sort query parameter has been set for this request. And if it is, it will run whatever is in the callback we provide. This callback receives both an instance of the query builder and the sort value as its arguments.

This callback receives both an instance of the QueryBuilder and the sort value as its arguments. Next, we're going to use a switch statement to check the value of the sort variable so we can run the appropriate orderBy query for it. Next, let's add the case statements for our three columns. The title is simple. We'll just inline our orderBy statement and pass in the direction from the request. For the status, we could do the same thing and simply order it by the status column. However, I want to customize how this column is sorted. So let's just add an orderByStatus scope call for now and pass in the direction.

However, I want to customize how this column is sorted. So let's just add an orderByStatus scope call for now and pass in the direction. Now, ordering by the activity is obviously not as simple. Our activity column includes counts for both votes and comments. So we'll need to find a meaningful way to order those together somehow. Again, let's just add an orderByActivity scope for now, passing in the direction. And now if we go back to the browser and sort the title ascending, we can see that it's working. And if we click it again, we're sorting descending. Perfect.

And if we click it again, we're sorting descending. Perfect. However, if we sort by the status, we get an error. Call to undefined method orderByStatus. And that makes sense since we haven't implemented that scope yet. Let's go to our Feature model and add that scope now. public function scopeOrderByStatus(). And that'll take an instance of the query builder as well as the direction. So as I already mentioned, we can simply order the status by the status column. Let's try that first.

So as I already mentioned, we can simply order the status by the status column. Let's try that first. Query orderBy status, and then we'll pass in the direction. And now if we go to the browser and hit refresh, this certainly works. We're seeing approved features first, then completed features and finally requested features. But this isn't great because in this app, features go from being requested to being approved to being completed. And it would be nice if our status column ordered in the same way. Let's update our query to order the status using a custom algorithm. To do this, we'll use the orderBy method, passing in a raw query as our first argument,

Let's update our query to order the status using a custom algorithm. To do this, we'll use the orderBy method, passing in a raw query as our first argument, which will include a case statement. When the status equals requested, we'll return the number one for approved, we'll return the number two, and for completed, we'll return the number three. Finally, we'll pass in the direction as the second argument. And if we go back to the browser and hit refresh, we're now getting our requested features first. And if we go to page four, we can see the approved features are listed next. And finally, we can see the completed features listed last. Our new custom algorithm is working.

And finally, we can see the completed features listed last. Our new custom algorithm is working. And just so you know, sorting descending works perfectly as well. And this is because we're now sorting by the number that we assigned each status instead of sorting the status alphabetically. Before we move on to sorting by the activity, I want to quickly show you how we can create an index for this custom algorithm. First, let's copy our case statement. Next, let's go to our features table migration and add a new raw index. We'll paste in the case statement.

Next, let's go to our features table migration and add a new raw index. We'll paste in the case statement. And finally, we'll provide an index name. And that's it. This can be a super helpful optimization to make if you're using a custom sorting algorithm like this on a large data set. OK, now for the most interesting column to sort, the activity column. We need to somehow add the votes and comments together so that we can order by that value. However, to me, a vote shouldn't be worth as much as a comment. What I'd like to do is assign a higher ranking to the comments when doing this calculation.

However, to me, a vote shouldn't be worth as much as a comment. What I'd like to do is assign a higher ranking to the comments when doing this calculation. For example, I think each comment should be worth twice as much as a vote. Let's go back to our Feature model and give this a try. We'll start by adding our new orderByActivity scope, and that'll take an instance of the query builder as well as the direction. And now let's think about the calculation we want to make. We want the votes count plus the comments count multiplied by two. How on earth are we going to do this in a database query? Well, it's actually easier than you might think,

How on earth are we going to do this in a database query? Well, it's actually easier than you might think, since we're already calculating our vote and comment counts in our query. Eloquent is running a subquery for each relationship to get the count and then assigning the count to the votesCount and the commentsCount columns. And we can use those columns in our order by activity calculation. Like with our status sort algorithm, we'll use the orderBy method, passing in a raw query as our first argument. Then we'll add our calculation as an expression to this query. votesCount plus commentsCount multiplied by two.

And of course, if we sort descending, we're now getting our lowest activity features first. Let's make a quick change to verify that this is actually working properly. Let's go to our features blade view and display this calculated value. featureVotesCount plus the featureCommentsCount multiplied by two. And if we go back to the browser and hit refresh, we can see our calculated activity value. Let's check our math. Two comments multiplied by two is four plus 29 equals 33. That's right. Five comments multiplied by two is 10 plus 50 is 60. Correct again.

So if you're doing something like this and performance becomes an issue, that's a great use case for some denormalization. Simply pre-compute and cache the activity ranking as an index. Create the activity ranking as an actual column in the features table, and then update it anytime someone votes or comments on that feature.