Excluding Holidays from my holidays table from date range
Hi I am currently trying to get a list of dates from today's date to the end of the year where the clinic is open based on the days and I would like to exclude out the holidays of the country which is stored in my holidays table. Currently, I am able to get the list of dates based on the operating days of the clinic, however, it did not exclude the dates that are stored in the holiday table.
Below is my codes in my controller class:
private function getHolidays()
{
$holidaysList = Holiday::where('clinic_id', '=', $_SESSION['clinic_ID'])->get();
$holidayArray = json_decode($holidaysList, true);
foreach($holidayArray as $holiday){
$holidays[] = $holiday['date'];
}
return $holidays;
}
public function getDays($workday, $dateFrom, $dateTo)
{
$holidays = $this->getHolidays();
$startDate = new DateTime($dateFrom);
$endDate = new DateTime($dateTo);
$interval = new DateInterval('P1D');
$dateRange = new DatePeriod($startDate, $interval, $endDate);
$results = [];
foreach ($dateRange as $date) {
$name = $date->format('l');
if (in_array($name, $workday) && !in_array($date->format('Y-m-d'), $holidays)) {
$results[] = $date->format('Y-m-d');
}
}
return $results;
}
Below are the dates stored in my holiday table
1 "New Year's Day" "2021-01-01"
2 "Chinese New Year" "2021-02-12"
3 "Chinese New Year" "2021-02-13"
4 "Good Friday" "2021-04-02"
5 "Labour Day" "2021-05-01"
6 "Hari Raya Puasa" "2021-05-13"
7 "Vesak Day" "2021-05-26"
8 "Hari Raya Haji" "2021-07-20"
9 "National Day" "2021-08-09"
10 "Deepavali" "2021-11-04"
11 "Christmas Day" "2021-12-25"
I don't think there is any efficient way to get this done. It is more about database design rather then the ORM. A quick inefficient way is to use ->whereNotIn('date',$holidays) and get it done.
Efficient way
Its not that straightforward to get it working efficiently. When you have to work with dates like this, where type of day (weekday/weekend), holidays, season etc play a role in business logic and fetching queries, you should create a calendar table.
A calendar table contains all the dates with meta date columns like is_weekend, is_holiday, is_last_day_of_week, is_last_day_of_month etc.
Then you simply make a query on calendar table filtering holidays and join your target table to get results
Also, you should keep the date column in target table reference to the calendar table for better performance.
Thanks for your input! I have managed to solve it as it was a formatting issue I was facing in my getHolidays() method.
I have made the following changes to the codes shown below and it is working fine.
$holidaysList = Holiday::where('clinic_id', '=', $_SESSION['clinic_ID'])->get();
$holidayArray = json_decode($holidaysList, true);
foreach($holidayArray as $holiday){
//Convert each data from table to Y-m-d format to compare
$holidays[] = date('Y-m-d', strtotime($holiday['date']));
}
return $holidays;
