How can i make this console query into laravel function query builder

Question

[user checks relation] [1]checks table users tablegot this console query to get the result i want but can't figure out how to make it a function in laravel.

select DATE(c.check_in),
       group_concat(users.id),
       sec_to_time(sum(TIME_TO_SEC(c.check_hours))) AS total_time
from users
         join checks c on users.id = c.user_id
where c.check_hours is not null
  and DATE(c.check_in) = '2022-11-17'
group by DATE(c.check_in)

DB::connection()->enableQueryLog();
        $today = Carbon::yesterday();
        $asdf = User::query()
            ->byNotWhereAdmin()
            ->with(['checks' => function ($query) {
                $query->where('check_hours', '08:00')
                    ->where('check_hours', '!=', null)
                    ->selectRaw('sum(TIME_TO_SEC(`check_hours`)) as total_time')
                    ->whereDate('check_in', '2022-11-17');
            }])
            ->get();

i did smth like this but cant figure out how to make the selectRaw() method to make a sum of times

sec_to_time(sum(TIME_TO_SEC(c.check_hours))) AS total_time

the result should be like this:

DATE(c.check_in) group_concat(users.id)         total_time
2022-11-17,      "2,8,11,5,15,16,4,6,14,7,13",  88:00:00

console results

I got a solution by changing the query from Users to Checks smth liek this:

 Check::query()
        ->select(DB::raw('DATE_FORMAT(check_in, "%d-%b-%Y") as datetime'))
        ->selectRaw('sec_to_time(sum(time_to_sec(`check_hours`))) as total_time')
        ->with('user')
        ->where('check_hours', '!=', null)
        ->when($this->selectDate && $this->selectDate2, function ($query) {
            $query->whereBetween('check_in', [$this->selectDate, Carbon::parse($this->selectDate2)->addDays(1)]);
        }, function ($query) {
            $query->whereBetween('check_in', [$this->selectDate, $this->selectDate2]);
        })
        ->groupBy('check_in', 'check_hours')
        ->get();

and i did whereBetween to make a range date to search

Answer 1

I got a solution by changing the query from Users to Checks something like below:

Check::query()
    ->select(DB::raw('DATE_FORMAT(check_in, "%d-%b-%Y") as datetime'))
    ->selectRaw('sec_to_time(sum(time_to_sec(`check_hours`))) as total_time')
    ->with('user')
    ->where('check_hours', '!=', null)
    ->when($this->selectDate && $this->selectDate2, function ($query) {
        $query->whereBetween('check_in', [$this->selectDate, Carbon::parse($this->selectDate2)->addDays(1)]);
    }, function ($query) {
        $query->whereBetween('check_in', [$this->selectDate, $this->selectDate2]);
    })
    ->groupBy('check_in', 'check_hours')
    ->get();

and I add whereBetween to make a range date to search