Question 15332 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel get weekday (Monday-Friday) and unset any weekday from date_range

here I have a lot of trouble finding weekday data (Monday – Friday) from date_range and then deleting one of those weekdays.

$dateRange = $request->date_range; //25-12-2023 - 31-12-2023
$query = Presence::orderBy('start_time', 'DESC')
$dateSegments = explode(' - ', $dateRange);
        if (count($dateSegments) == 2) {
            $query->whereDate('date', '>=', Carbon::createFromFormat('d-m-Y', $dateSegments[0]));
            $query->whereDate('date', '<=', Carbon::createFromFormat('d-m-Y', $dateSegments[1]));
        }
$query->join('users', 'presences.user_id', 'users.id')
    ->groupBy('user_id')
    ->select(
        'presences.user_id',
        'users.name as user_name',
    );
$perUser = $query->get();

Output this query:

[
    [
        'id' => 1,
        'user_id' => 10
        'date' => '2023-12-25' //Monday
    ],
    [
        'id' => 2,
        'name' => 10
        'date' => '2023-12-26' //Tuesday
    ],
    [
        'id' => 3,
        'name' => 10
        'date' => '2023-12-27' //Wednesday
    ],
    [
        'id' => 4,
        'name' => 10
        'date' => '2023-12-28' //Thursday
    ],
    [
        'id' => 5,
        'name' => 10
        'date' => '2023-12-31' //Sunday
    ],
];

I have output from holidays table;

    [
        [
            'id' => 1,
            'name' => 'Christmas',
            'date' => '2023-12-25' //Monday
        ],
        [
            'id' => 1,
            'name' => 'Independent Day',
            'date' => '2023-12-27' //Wednesday
        ],
    ];

Map

$list = $perUser->map(function ($item) {
//this is count day without Saturday + Sunday + holiday
$item->active_day = Holiday::whereDate(‘date’, ‘>=’, Carbon::createFromFormat(‘d-m-Y’, $dateSegments[0]))->whereDate(‘date’, ‘<=’, Carbon::createFromFormat(‘d-m-Y’, $dateSegments[1]))->count();
return $item;
});
return $list

Output what i want:

[
    {
      "user_id": 10,
      "user_name": "Brian",
      "active_day": 2 //this is count day without Saturday + Sunday + holiday
    }
]
Tags:

2

Answers


  1. 0

    I might suggest to do all of this with sql:

    Exclude saturday and sunday directly from the query by adding this where condition:

    DAYOFWEEK(presence.date) IN (1, 7);

    Then you can exclude holidays by adding the following where condition with a subquery:

    presence.date NOT IN (SELECT date FROM holidays)

    All of this made with Laravel query builder:

    ->whereRaw('DAYOFWEEK(date) NOT IN (1,7)')
->whereNotIn('date', function ($query) {
    $query->select('date')->from('holidays');
})

    One last thing, you could use this statement:

    ->whereBetween('date', [$dateSegments[0], $dateSegments[1]])

    but I’m not 100% sure it will work as you may expect.

    Login or Signup to reply.
  2. 0

    Fetch the days to remove from the list,

    $daysToRemove = $holiday->where('date', '>=', now()->parse($dateSegments[0])->format('d-m-Y'))->where('date', '<=', now()->parse($dateSegments[1])->format('d-m-Y'))->map(function($item){
     return now()->parse($item['date'])->format('l');
  })->merge(['Saturday','Sunday'])->toArray();

    Map the list and filter with condition that the days need to be removed

    $activedays = $perUser->map(function ($item) use ($daysToRemove) {
    return (!in_array(now()->parse($item['date'])->format('l'), $daysToRemove)) ? $item : null;
})->filter()->countBy('name');

    Result will be,

    [
  10 => 2, // active_day count
]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search