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

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

[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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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


  2. 0

    I think you should rely to Laravel’s QueryBuilder instead of Eloquent. Given we are working with aggregates instead of mapping query result to objects.

    DB::table('users')
  ->join('checks as c', 'users.id', '=', 'c.user_id')
  ->whereNotNull('c.check_hours')
  ->where(DB::raw('date(c.check_in)'), '2022-11-17')
  ->groupBy(DB::raw('date(c.check_in)'))
  ->select(
      DB::raw('date(c.check_in) as date'),
      DB::raw('group_concat(users.id) as user_ids'),
      DB::raw('sec_to_time(sum(time_to_sec(c.check_hours))) as total_time')
    )
  ->get();

    Note: DB::raw is a powerful tool, though do avoid passing user-supplied parameter into it. In this case we use it only for calling mysql functions.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search