We had an interesting request come in from a client today. They have a big list of user birthdays that they want to display on the front end of the ir employee intranet site. In order to protect employees actual age they only want the month and day of the employees birthday to show, that way people can essentially wish them happy birthday but not know their true age. In essence the client wanted a list of birthdays like this:
Employee 1 12/1
Employee 2 12/4
Employee 3 12/5
To add a little complexity to this task the client wanted us to sort the birthdays by day so that they were listed in chronological order. Sorting this list is pretty easy with the orderBy command, this is what we came up with first:
$birthdays = User::where('birth_month', $current_month)->where('active', 1)->orderBy('birthdate', 'asc')->get();
The issue we ran into with the code above is that it sorted by year, then day. Even though we are not showing the year on the front end, it is still saved in the database as a full date/time string of day-month-year. The code above ended up giving us a list like this:
Employee 1 12/4/1990
Employee 2 12/1/1992
Employee 3 12/5/1992
Any employees who had the same birth year were grouped together and then sorted by date. To fix this we had to change from orderBy to orderByRaw. In practice the code we ended up using looks like this:
$birthdays = User::where('birth_month', $current_month)->where('active', 1)
->whereMonth('birthdate', $current_month)
->orderByRaw('DATE_FORMAT(birthdate, "%m-%d")')
->get();
The code above ended up working perfectly for the client. You will see that the orderByRaw code above uses the m-d portion of the date/time string but it does not use the year portion. With this code we are left with a list like this:
Employee 1 12/1/1992
Employee 2 12/4/1990
Employee 3 12/5/1992