勤怠打刻機能 休憩時間を算出させてviewに表示させたい

Question

実現させたいこと

現在勤怠打刻機能を作成しております。
休憩時間を何回でも取得できるように勤怠打刻テーブル(stamps)、休憩打刻テーブル(rests)を1対多のリレーションをさせています。
現在は(stamps)にカラム(total_rest)を持たせ、(rests)で休憩時間を算出してカラム(rest_time)に値を入れてwhere('stamp_id',$timestamp->id)->sum('rest_time')として合計値を('stamps.total_rest')に入れています。
やりたいこととしては(stamps.total_rest)を使わずに休憩テーブル(rests)の同じ外部キー(stamp_id)を持った休憩時間の合計値を算出してviewに表示させたいです。

テーブル仕様

ソースコード

public function showTable()
    {
        $user = Auth::user();
        $date = date("Y-m-d");
        $stampDate = Stamp::select('stamp_date')->get();
        if (!$stampDate) {
            return redirect()->back()->with(['message' => '勤務履歴がありません', 'status' => 'alert']);
        }

        $stamps = Stamp::join('users', 'users.id', 'user_id')
            // ->join('rests','stamps.id','stamp_id')
            ->where('stamp_date', $date)
            ->orderBy('stamps.updated_at','asc')
            ->paginate(5);

        return view('showTable', compact('stamps', 'date'));
    }

    public function search(Request $request)
    {
        $date = $request->date;
        $stamps = Stamp::join('users', 'users.id', 'user_id')
            ->where('stamp_date', $date)
            ->orderBy('stamps.updated_at', 'asc')
            ->paginate(5);

        return view('showTable', compact('stamps', 'date'));
    }

<x-app-layout>
    <x-slot name="header">
        <div class="flex justify-between">
            <h2 class="font-semibold text-xl text-gray-800 leading-tight">
                勤務一覧
            </h2>
            <div class="text-xl">
                <p>本日の日付</p>
                <div>{{date('Y-m-d')}}</div>
            </div>
        </div>
    </x-slot>

    <div class="py-12">
        <div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
            <section class="text-gray-600 body-font">
                <div class="container px-5 mx-auto">
                    <div class="flex flex-col justify-around text-center w-full mb-20">
                        <h1 class="sm:text-4xl text-3xl font-medium title-font mb-2 text-gray-900">
                            {{$date}}の勤務一覧
                        </h1>
                        <form method="post" action="{{route('search')}}">
                            @csrf
                            @method('post')
                            <div>
                                <label for="date" class="mr-2">日付を選択して下さい</label>
                                <input type="date" name="date" value="date" id="date" class="mr-2">
                                <button class="text-white bg-blue-500 border-0 py-2 px-6 focus:outline-none hover:bg-blue-600 rounded">検索</button>
                            </div>
                        </form>
                    </div>
                    <div class="lg:w-2/3 w-full mx-auto overflow-auto">
                        <div class="text-xl text-blue-600 ml-4">{{$date}}勤務一覧を表示中</div>

                        <table class="table-auto w-full text-left whitespace-no-wrap">
                            <thead>
                                <tr>
                                    <th class="px-4 py-3 title-font tracking-wider font-medium text-gray-900 text-sm bg-gray-100 rounded-tl rounded-bl">名前</th>
                                    <th class="px-4 py-3 title-font tracking-wider font-medium text-gray-900 text-sm bg-gray-100">勤務開始</th>
                                    <th class="px-4 py-3 title-font tracking-wider font-medium text-gray-900 text-sm bg-gray-100">勤務終了</th>
                                    <th class="px-4 py-3 title-font tracking-wider font-medium text-gray-900 text-sm bg-gray-100">休憩時間</th>
                                    <th class="px-4 py-3 title-font tracking-wider font-medium text-gray-900 text-sm bg-gray-100">勤務時間</th>
                                </tr>
                            </thead>
                            <tbody>
                                <tr>
                                    @foreach($stamps as $stamp)
                                    <td class="px-4 py-3">{{$stamp->name}}</td>
                                    <td class="px-4 py-3">{{$stamp->start_work}}</td>
                                    <td class="px-4 py-3">{{$stamp->end_work}}</td>
                                    <td class="px-4 py-3">{{$stamp->total_rest}}</td>
                                    <td class="px-4 py-3">{{gmdate("H:i:s",(strtotime($date.$stamp->end_work)-strtotime($date.$stamp->start_work)))}}</td>
                                </tr>
                                @endforeach
                            </tbody>
                        </table>
                        {{$stamps->links()}}
                    </div>
                </div>
            </section>
        </div>
    </div>
</x-app-layout>

試したこと

join('rests','stamps.id','stamp_id')としてviewの部分でforeach($stamp->rests as $rest)で回して算出してみたが１レコードずつ表示され休憩時間の合計値を取得できない。

なにかアドバイス頂けると幸いです。
よろしくお願い致します。

Answer 1

やりたいこととしては(stamps.total_rest)を使わずに休憩テーブル(rests)の同じ外部キー(stamp_id)を持った休憩時間の合計値を算出してviewに表示させたいです

restsテーブルに対して、stamp_idごとの合計休憩時間を算出するクエリーと
テーブルstampsをjoinすれば良さそうな気がする。
サブクエリーとのjoinには、メソッド joinSub() leftJoinSub() rightJoinSub()を使うけど、
必ずしも休憩時間があるわけではないだろうからstampsテーブルに対するleftJoinSub()ではないだろうか。

select stamp_id, sum(rest_time) as sum_rest_time
from rests
group by stamp_id

となるサブクエリーを表現すればいいんだとすると、

$rests = DB::table('rests')->select('stamp_id', DB::raw('SUM(rest_time) as sum_rest_time'))->groupBy('stamp_id');

みたいになるのかな。（机上のみ）

SQLがわかっててクエリビルダでの表現がわからないときは、
こちらを参考にすると良いです：

Laravel クエリビルダ記法まとめ
→サブクエリをJOIN を参照

Comments

[TAKU9]

問題解決できました。
いままでクエリに対してあまり理解ができていなかったのでいい機会となりました。引き続き理解を深めていきます。ありがとうございました。