Laravel eloquentによりデータをgrepした後特定の条件を抽出する

Question

現在Laravelの勉強中です。
今回Controllerで下記の予約テーブルを元にgrepしてそれぞれを加工しました。
id|user_id|date(日付)|count(人数)|
|:--|:--:|--:|
|1|1|2021-06-12 18:00|1|
|2|1|2021-06-17 19:00|2|
|3|1|2021-06-19 19:00|4|
|4|1|2021-06-19 20:00|2|

①データベースからdate(日付)ごとにグループ化して取得

No	date
1	2021-6-12
2	2021-6-17
3	2021-6-19

②日付ごとの行数を取得

No	行数
1	1
2	1
3	2

③日付ごとの人数を合計したものを取得

No	人数
1	1
2	2
3	4

こちら３点の処理を実装したものが下記のコードになります。

namespace App\Http\Controllers\Admin;

use DB;
use App\Model\User;
use App\Model\Admin;
use App\Model\Reservation;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;

----中略------------
    public function eventsmonth()
    {
        $admin_id = Auth::id();
        //予約テーブルから①の日付ごとになるように取得
        $dates = Reservation::where('admin_id', $admin_id)->select('date', 'count', DB::raw('DATE_FORMAT(date, "%Y-%m-%d") as eventsday'))->get('date')->groupBy('eventsday');
   
        $days = [];
        $pairs = [];
        $counts = [];
        foreach($dates as $key => $date)
        {
            //②の処理日付ごとの行数を取得
            $pair = $date->count();
            $pairs[] = $pair;
            $day = $key;
            $days[] = $day;
            $counter = 0;
            for($i=0; $i<$pair; $i++)
            {
                //③の処理　日付ごとの人数の合計を計算
                $count = $date[$i]->count;
                $counter += $count;
            }
            $counts[] = $counter;
        }
        dump($days);
        dump($pairs);
        dd($counts);

    }

こちらで実装したものが下記のように取得できました。
なお実際に使用しているテーブルとは値が違うため、内容は異なります。

array:4 [▼
  0 => "2021-06-23"
  1 => "2021-06-17"
  2 => "2021-06-18"
  3 => "2021-06-19"
]
array:4 [▼
  0 => 2
  1 => 2
  2 => 2
  3 => 3
]
array:4 [▼
  0 => 8
  1 => 4
  2 => 26
  3 => 10
]

しかし、こちらの処理では繰り返し処理が重なっていたり、変数などが入り混じって見にくかったりします。
この処理をもう少し短く分かりやすく書く方法はありますか？

Comments

[退会済みユーザー]

結果の再加工は非効率なので、ズバリ欲しいデータが得られるクエリーをクエリビルダで作るんじゃないかなと。

[退会済みユーザー]

結果の再加工は非効率なので、ズバリ欲しいデータが得られるクエリーをクエリビルダで作るんじゃないかなと。

[royal1997]

そのクエリビルダのやり方が分からない状態です。

Answer 1

下記の処理で私がしたかったことを実装することができました。

        $dates = Reservation::where('admin_id', $admin_id)->selectRaw("DATE_FORMAT(date, '%Y-%m-%e') as eventsday ,COUNT(count) as pairs ,SUM(count) as total_count")->groupBy('eventsday')->get();