I have a table name leave_requests like this [leave request table][1] [1]: https://i.stack.imgur.com/B7VvC.png from above table I want to get total leaves between two time like (From 2021-12-02 to 2021-12-07) only when I have status "Approved", I have done this so far

LeaveRequest
    ::where("start_date", ">=", $request->from)
    ->where("end_date", "<=", $request->to)
    ->where("status", "approved")
    ->get()

as from above code I am matching two date conditions, this is the output:

{
    "id": 31,
    "code": "1o1545",
    "organization_id": 1,
    "employee_id": 7,
    "leave_id": 1,
    "start_date": "2021-12-03",
    "end_date": "2021-12-06",
    "reason": "sick",
    "status": "approved",
    "created_at": "2021-12-01 21:01:59",
    "updated_at": "2021-12-01 21:02:14",
    "leave_days": 4,
    "is_archived": false,
    "is_inactive": true,
    "is_geofencing": false,
    "is_tax_exempted": false,
    "full_name": "",
    "current_position": null
},
{
    "id": 34,
    "code": "mygm39",
    "organization_id": 1,
    "employee_id": 7,
    "leave_id": 1,
    "start_date": "2021-12-04",
    "end_date": "2021-12-04",
    "reason": "ljk",
    "status": "approved",
    "created_at": "2021-12-03 09:35:59",
    "updated_at": "2021-12-03 09:35:59",
    "leave_days": 1,
    "is_archived": false,
    "is_inactive": true,
    "is_geofencing": false,
    "is_tax_exempted": false,
    "full_name": "",
    "current_position": null
},
{
    "id": 35,
    "code": "m1e8zy",
    "organization_id": 1,
    "employee_id": 7,
    "leave_id": 1,
    "start_date": "2021-12-07",
    "end_date": "2021-12-07",
    "reason": "jj",
    "status": "approved",
    "created_at": "2021-12-03 09:36:43",
    "updated_at": "2021-12-03 09:36:43",
    "leave_days": 1,
    "is_archived": false,
    "is_inactive": true,
    "is_geofencing": false,
    "is_tax_exempted": false,
    "full_name": "",
    "current_position": null
}

The problem is that it will not give me rows where date is present like date is starting from (2021-12-02) but it didnot give me that record plus how to count leaves from leaves column. hope I am clear and please someone help me here I am new to laravel thanks.

CodePudding user response：

Try this query:

LeaveRequest
    ::where("start_date", ">=", $request->from)
    ->where("end_date", "<=", $request->to)
    ->where("status", "approved")
    ->select('leave_id', \DB::raw('count(*) as total_leaves'))
    ->groupBy('leave_id')
    ->get();

CodePudding user response：

I have finally found a solution to this below is code attached for someone in future:

use App\Models\LeaveRequest;
use Carbon\CarbonPeriod;

$total_leaves = 0;
    LeaveRequest
    ::when($request->from && $request->to, function($query) use($request) {
        $query
            ->whereDate("leave_requests.start_date", "<=", $request->to)
            ->whereDate("leave_requests.end_date", ">=", $request->from);
    })
    ->when($request->from && !$request->to, function($query) use($request) {
        $query
            ->whereDate("leave_requests.start_date", "<=", $request->from)
            ->whereDate("leave_requests.end_date", ">=", $request->from);
    })
    ->where("leave_requests.status", "approved")
    ->get()
    ->map(function($leave_request) use(&$total_leaves, $request) {
        // echo $leave_request->start_date . " " . $leave_request->end_date . "<br/>";
        if(!$request->to && Carbon::parse($request->from)->betweenIncluded($leave_request->start_date, $leave_request->end_date)) {
            $total_leaves  ;
        } else {
            // echo $leave_request->start_date . " " . $leave_request->end_date . "<br/>";
            $DB_periods = CarbonPeriod::create($leave_request->start_date, $leave_request->end_date);
            $REQUEST_periods = CarbonPeriod::create($request->from, $request->to);
            foreach ($DB_periods as $period1) {
                // echo "DB-> " . $period1->format('Y-m-d') . "<br>";
                foreach($REQUEST_periods as $period2) {
                    // echo "Request-> " . $period2->format('Y-m-d') . "<br>";
                    if($period1 == $period2) {
                        $total_leaves  ;
                    }
                }
            }
        }
    });

    return "Total Leaves: " . $total_leaves;

As I have told I am new to programming and make this solution after many hours of hardships so point is this code is lengthy and maybe not such good practice, so if any pro can minimize it thanks.