Image Alt

fofx Academy

  /  Laravel   /  How To Get Upcoming Birthdays using Laravel’s Eloquent

How To Get Upcoming Birthdays using Laravel’s Eloquent

So apparently, I’m presently working on a project in which I had to get the upcoming birthdays of employees whereby the query had to search for the latest birthday using the month and date properties as the filter and I thought of different ways to get the simplest and easiest way to go about it.

In the course of my multiple searches, I came up with a simple query.

I started off by bringing in carbon into my model:

use Illuminate\Support\Carbon;

Then in my function called getUpcomingBirthdays() I declared a variable:

$date = now();

Note that the now()function comes from carbon: it gets the present date. You can read more about carbon here https://carbon.nesbot.com/.

Then my query:

return Employee::whereMonth(‘date_of_birth’, ‘>’, $date->month)

           ->orWhere(function ($query) use ($date) {

               $query->whereMonth(‘date_of_birth’, ‘=’, $date->month)

                   ->whereDay(‘date_of_birth’, ‘>=’, $date->day);

           })

           ->orderByRaw(“DAYOFMONTH(‘date_of_birth’)”,’ASC’)

           ->take(3)

           ->get();

So the query above simply explains that:

For each employee in the database, using the whereMonth() method which is used to compare a columns value against a specific month of the year  (you can read more about it here: https://laravel.com/docs/5.8/queries), the month of  date_of_birth in the database is greater than the present month.

In case you are confused where the month property came from, it is a property of carbon. Awesome right? I know.

Moving on, we further extend our query:

We use the conditional or Where()method,with a function in the query which takes in a parameter $query and then we use the carbon now() function which we stored in the $date variable above which we declared earlier.

In our function, we now get the month from the date_of_birth using the whereMonth() method and set it to be equal to the present month as the second condition to be met.

And then we say OR where the day (using orWhereDay()method) is greater or equal to the present day. The WhereDay()  is used to compare a columns value against a specific day of the month and that’s a wrap for our condition in the function.

Now, what next?

We need to order the birthdays so they come out in ascending order i.e from the most recent birthday to the later, hence, we use the orderByRaw() method which may be used to set a raw string as the value of the orderBy clause:

This method takes in two parameters in our case which are:

  • the MySQL DAYOFMONTH() function which is used to retrieve the day of the month of the given date and:
  • The ASC which sets the birthdays to show up in ascending order.

I just want 3 upcoming birthdays to show up in my UI so I limited the query to spit out just 3 using the take()method. And finally, we end our query with the get()method to retrieve all our results.

And that’s it guys, thanks for reading and I hope this article was helpful.