how to get date in year and month format e.g 2017-04

How do i apply it here?

$attendance = \Carbon\Carbon::parse(Attendance::find($request->input('user_id')))->whereDate('date', format('Y-m'), '=', $request->input('month_year'))->count('present');

Ok, that is a different problem, which you could solve like this:

[$year, $month] = explode('-', $request->input('month_year')); // e.g. [2021, 7]
$attendance = Attendance::selectRaw('count(present) as present_count')
	->whereMonth('date', $month)
	->whereYear('date', $year)
	->where('user_id', $request->input('user_id'))
	->first();

i am getting

ErrorException
Undefined offset: 1

for

[$year, $month] = explode('-', $request->input('month_year'));

What is the format of $request->input('month_year’)?

format is 2017-04

The explode expression should make two variables in that case. Maybe wrap in try/catch an bail out if the year and month are not set

It's returning my present count() as 0

$date = MonthYear::find($request->input('month_year'));
        // [$year, $month] = explode('-', $request->input('month_year')); // e.g. [2021, 7]
        $attendance = Attendance::selectRaw('count(present) as present_count')
            ->whereDate('date', $date)
            ->where('staff_id', $request->input('user_id'))
            ->get();

I was getting same results, It’s not counting not null values under Present

Can you dump the query that is actually being executed; whenever you use whereMonth and whereYear; use the following immediately before the query above:

DB::listen(fn ($query) => dump([$query->sql, $query->bindings]));

I don't understand how to apply it. can you expatiate a bit?

It works this way, but upon adding the whereMonth and whereYear, the counting returns to 0

$attendance = Attendance::selectRaw('count(present) as present_count')
            // ->whereMonth('date', $month)
            // ->whereYear('date', $year)
            ->where('staff_id', $request->input('user_id'))
            ->get();

This is how you would implement the Query listener (temporarily) - it should dump the query SQL and bindings. It will hopefully provide some clue why the query is returning 0 when the Month and Year constraints are applied:

DB::listen(fn ($query) => dump([$query->sql, $query->bindings]));

$attendance = Attendance::selectRaw('count(present) as present_count')
            ->whereMonth('date', $month)
            ->whereYear('date', $year)
            ->where('staff_id', $request->input('user_id'))
            ->first();

i get this before the main query

array:2 [▼
  0 => "select count(present) as present_count from `attendance` where month(`date`) = ? and `staff_id` = ?"
  1 => array:2 [▼
    0 => "{"id":1,"working_days":"26","month_year":"2021-06","created_at":"2021-07-08T02:01:42.000000Z","updated_at":"2021-07-08T02:14:51.000000Z"}"
    1 => "1"
  ]
]

Ok, clearly this "{"id":1,"working_days":"26","month_year":"2021-06","created_at":"2021-07-08T02:01:42.000000Z","updated_at":"2021-07-08T02:14:51.000000Z"}" is not a month number! How are you getting the month number???

$date = MonthYear::find($request->input('month_year'));

DB
"id" => 1
    "working_days" => "26"
    "month_year" => "2021-06"
    "created_at" => "2021-07-08 02:01:42"
    "updated_at" => "2021-07-08 02:14:51"

Fixed it!

$date = MonthYear::find($request->input('month_year'));
        [$year, $month]= explode('-', $date->month_year);// e.g. [2021, 7]

        $attendance = Attendance::selectRaw('count(present) as present_count')
            ->whereMonth('date', $month)
            ->whereYear('date', $year)
            ->where('staff_id', $request->input('user_id'))
            ->get();

Thank you!

What is this MonthYear class; and why is it necessary in this context? You mentioned earlier that the $request->input('month_year') was in the form 2021-06 already, right? Was it an issue that the month number was 06 rather than 6; did it simply require casting to int:

[$year, $month]= explode('-', $request->input('month_year'));// e.g. [2021, 7]

$attendance = Attendance::selectRaw('count(present) as present_count')
    ->whereMonth('date', (int) $month)
    ->whereYear('date', (int) $year)
    ->where('staff_id', $request->input('user_id'))
    ->get();

Yes it was an issue with month number 06. how do i get my $attendance value as (int) present_count to be used outside the query. E.g $attendance->(int) present_count to give me 2 just the int.

Try to use count Builder method again:

$attendance = Attendance::whereMonth('date', (int) $month)
    ->whereYear('date', (int) $year)
    ->where('staff_id', $request->input('user_id'))
    ->count('present');