However, with natural sorting, it lists the iPhone 3 first because it's comparing the digits in the version number as a whole. The number 3 comes before the number 11. And php actually has built-in support for natural sorting. For example, let's quickly create an array with our two iPhone versions in it. Now, let's sort that array and return the results. And if we hit refresh in the browser, we can see that it's sorted the array listing the iPhone 11 first. However, now let's add the SORT_NATURAL flag to the sort function. And if we hit refresh in the browser again, we can see it's now sorting our array the way we want it, with the iPhone 3 first. Pretty cool, right? So, how do we do this in the database?

even if we only ever want to display 15 results at a time. So, what options do we have? Well, the way I've solved this in the past is using a user-defined database function. Let's give that a try by creating a custom natural sort function in our database. Let's go to the command line and generate a new migration. php artisan make:migration addNaturalSortFunction. Okay, now let's go to that migration file. Now, for this lesson, we're not going to go through the whole process of actually writing the natural sort function. I've simply prepared a snippet to paste in. And, as you can see, it's very long and complicated.

I've simply prepared a snippet to paste in. And, as you can see, it's very long and complicated. This function is a slightly modified version of the Drupal project's natural sort function. What's more important for this lesson is simply how we're creating the custom function. We're using the dbUnprepared method to do this, which will run a raw unprepared query against the PDO connection. And then we're executing a createFunction query, which is creating our new natural sort function. Finally, let's update our down migration to remove this function. dbUnprepared dropFunction ifExists naturalSort Okay, that's it. Let's run our migrations. php artisan migrate

Okay, that's it. Let's run our migrations. php artisan migrate Perfect. Our natural sort function is ready to go. Now, let's go back to our DevicesController and update our query. Let's remove the existing name orderBy. And then let's add a new raw orderBy, which calls our new natural sort function on the name column. And now if we go back to the browser and hit refresh, we can see our natural sorting is working. We can see that this page now starts with the iPhone 4 and ends with the iPhone 11 right after the iPhone 8. Very, very cool. And if we look at our queries in the Laravel debug bar, we can see the query we just generated.

Very, very cool. And if we look at our queries in the Laravel debug bar, we can see the query we just generated from devices orderBy naturalSort on the name column. And then we just have our pagination limit and offset. One quick note before we wrap up this lesson. If you have a large dataset, definitely consider creating an expression index for your column's natural sort. For example, in our devices migration, we could add an index using the rawIndex method and then call our naturalSort function on the name column. However, be aware that MySQL, at least at this time, does not let you create an expression index with a user-defined function like this. Postgres fortunately does allow you to do this.

However, be aware that MySQL, at least at this time, does not let you create an expression index with a user-defined function like this. Postgres fortunately does allow you to do this.