In this lesson, we're going to look at how to sort and filter data by anniversary dates. To illustrate this, I've set up this User's page, which shows their name, email address, and birthday. The first change we want to make is to update the page to order by the User's birthday. Let's go to the UserController and add a new orderByBirthday scope right before our existing orderByName. Now, let's go to the User model and create this scope. public function scopeOrderByBirthday, and that'll take an instance of the QueryBuilder. So, how exactly are we going to do this? We can't just order them by their birth date, since that includes the year. Instead, what we really want to do is order them by their birth month and day. And we can do this using the MySQL DATE_FORMAT function. We'll need to use an orderByRaw method to do this. query->orderByRaw, and then we'll

birth month and day. And we can do this using the MySQL date_format function. We'll need to use an orderByRaw method to do this. Query, orderByRaw, and then we'll call the date_format function on the birthday column using the month day format. And now, if we hit refresh in the browser, we can see that the birthday ordering is working. Let's take a look at our query in the Laravel debug bar. select * from users, order by date_format in the month day format, and then by their name ascending, and then we have our pagination limit and offset. And if we look at our combined query time, we can see that it's running at about 27 milliseconds. And while this certainly isn't slow, I think we can still make it faster. Let's go to the users table migration and add a new compound index. If you haven't watched the faster ordering using compound indexes lesson yet,

can still make it faster. Let's go to the users table migration and add a new compound index. If you haven't watched the faster ordering using compound indexes lesson yet, be sure to do that for more information on what we're about to do. As we learned in that lesson, in situations where you're ordering records by more than one column, you can create a compound index for those columns, and MySQL will use that when doing the sort. However, in this situation, it's a little more complicated. We're not only creating a compound index with multiple columns, we're also creating one with an expression. And we can still do this, but we need to use the rawIndex method instead. table, rawIndex, using the date format expression that we just wrote in our query. And then we include the name column as well. And then we'll give it the name, users_birthday_name_index. Now let's rerun our migrations in php artisan migrate.

wrote in our query. And then we include the name column as well. And then we'll give it the name, user's birthday name index. Now let's rerun our migrations in php artisan migrate. And if we go back to the browser, remembering that we're at a combined query time of about 27 milliseconds, and we hit refresh, we can see that we're now at a combined query time of only five milliseconds. Beautiful. Okay, now for something a little more complicated. Let's update our page to only list Users who have a birthday this week. Again, we'll start by adding a scope call to our query in the UsersController, whereBirthdayThisWeek. Now let's go to our User model and add this public function scopeWhereBirthdayThisWeek. And again, that'll take an instance of the QueryBuilder. So how exactly are we going to limit the records to only Users with a birthday this week? Well, we know that we need to ignore the year when doing this. So why don't we start

the query builder. So how exactly are we going to limit the records to only Users with a birthday this week? Well, we know that we need to ignore the year when doing this. So why don't we start with a whereBetween clause that checks to see if the User's birthday month and day is between the first and last day of this week. For example, if the first day of the week is May 25, and the last day of the week is May 31, we'll check to see if the User's birthday is between the digits 05-25 and 05-31. Let's try it. Query::whereRaw. And then we need to run an expression to calculate the birthday in the month day format. From there, we'll do a between check that includes our start and end dates as parameter bindings. Next, we'll use a Carbon::startOfWeek method to calculate the first day of the week. And we'll use the same month day format as the expression in our query. And finally, we'll duplicate this line and update it for the end of the week. And now if we hit

first day of the week. And we'll use the same month-day format as the expression in our query. And finally, we'll duplicate this line and update it for the end of the week. And now if we hit refresh in the browser, we can see that it's working. We're getting only the users that have a birthday this week, May 25th to May 31st. Let's take a look at our query in the Laravel debug bar. Select * from users where birth_date is between '05-25' and '05-31'. Pretty cool. However, there is one problem with this approach. If January 1st happens to land within our week, we'll get no records back. Let's try that for a second using the Carbon::setTestNow() helper. Carbon::setTestNow() and then we'll just pass through January 1st as our date. This just tells Carbon to act as if the current date is January 1st. And now if we hit refresh in the browser, we can see that we don't get any results back. And this makes sense. A between comparison doesn't

carbon to act as if the current date is January 1st. And now if we hit refresh in the browser, we can see that we don't get any results back. And this makes sense. A between comparison doesn't work when the first argument 12-30 is greater than the second argument 01-05. So unfortunately, this technique won't work. Let's try a different approach. Let's just comment out our carbon set test now call for now and let's remove our existing query. This time, instead of doing a whereBetween, what if we used a whereIn instead? After all, each week only has seven days. Let's try it. Query whereRaw and then we'll run an expression again to calculate the birthday in the month day format. Then we'll check if the result of that expression exists in one of the seven days of our week and then we'll pass through an array of our dates as our bindings. Finally, we just need to populate the dates variable and we can use carbon to do this. dates equals and

seven days of our week and then we'll pass through an array of our dates as our bindings. Finally, we just need to populate the dates variable and we can use Carbon to do this. dates equals and then we'll use Carbon to get the first day of the week. Next, we'll use the daysUntil method to generate a series of dates until the end of the week. And then we'll map over each day of the week and format them using the same monthDay format as the expression in our query. However, since the map function returns a generator, we need to call iterator_to_array on the dates variable to convert it to an array. That's it. Let's go to the browser and hit refresh. And that's working for our current week. If we look at our query in the Laravel debug bar, we can see the new whereIn clause, which includes a seven days of the week in the monthDay format. And if we change the current date back to January 1st and hit refresh again in the browser, we can see

the new where in clause, which includes a seven days of the week in the month day format. And if we change the current date back to January 1st and hit refresh again in the browser, we can see that we've now solved our new year's issue. If we go to the last page, we can see our users who have a birthday in December. However, at this point, you might want to reconsider the sorting algorithm to order by upcoming birthdays instead of just ordering by birthdays. And that problem presents some interesting challenges all on its own that I'm not going to cover in this particular lesson. However, I will include an orderByUpcomingBirthday scope in the source code for this lesson if you're interested. Okay, one last thing before we wrap up. Let's copy and paste our query into TablePlus and have a closer look at it. Let's comment out our where condition so we can focus on the orderBy statement. Now let's run and explain on this query. And as we expect, this