mysql - groupBy ('created_at' ) 在 laravel Eloquent 中使用子查询

Question

我正在为我的项目中的库存做一份报告。现在，我想根据日期之间显示 items。我想通过对 created_at 进行分组来在 View 中显示项目列表，如下所示-

我的数据库表结构是——

在 Controller 方法中，我写了-

public function inventorySubmit(Request $request)
{
    $itemId     = $request->item;
    $startDate  = Carbon::parse($request->start_date)->format('Y-m-d');
    $endDate    = Carbon::parse($request->end_date)->format('Y-m-d');
    $reportType = $request->report_type;

    if ($itemId) {
        $items = Item::where('id', $itemId)->whereBetween('created_at', [ $startDate, $endDate ])->where('status', 1)->get();
    }else{
        $items = Item::whereBetween('created_at', [ $startDate, $endDate ])->where('status', 1)->get();
    }
    return view('report.inventory.searched-result', compact('reportType', 'startDate', 'endDate', 'items'));
}

在 Blade 中，我显示为 -

<table class="table text-center" style="text-align: left; width: 100%;">
    <tr>
        <td style="font-weight: bold;">#</td>
        <td style="font-weight: bold;">Date</td>
        <td style="font-weight: bold;">Opening Balance</td>
    </tr>
    @foreach($items as $item)
    <tr>
        <td>{{ $loop->index + 1 }}</td>
        <td>{{ date('jS F, Y', strtotime($item->created_at)) }}</td>
        <td>
            <span>{{ $item->name }}</span> - <span>{{ $item->opening_balance }} <br></span>
        </td>
    </tr>
    @endforeach

</table>

Answer 1

像这样在 get 方法之后使用 laravel groupBy

Controller

public function inventorySubmit(Request $request)
{
    $itemId     = $request->item;
    $startDate  = Carbon::parse($request->start_date)->format('Y-m-d');
    $endDate    = Carbon::parse($request->end_date)->format('Y-m-d');
    $reportType = $request->report_type;

    if ($itemId) {
        $items = Item::select('*', DB::raw('Date(created_at) as date'))
                      ->where('id', $itemId)->whereBetween('created_at', [ $startDate, $endDate ])
                      ->where('status', 1)
                      ->get()
                      ->groupBy('date');
    }else{
        $items = Item::select('*', DB::raw('Date(created_at) as date'))
                      ->whereBetween('created_at', [ $startDate, $endDate ])
                      ->where('status', 1)
                      ->get()
                      ->groupBy('date');
    }
    return view('report.inventory.searched-result', compact('reportType', 'startDate', 'endDate', 'items'));
}

在 View 中

<table class="table text-center" style="text-align: left; width: 100%;">
    <tr>
        <td style="font-weight: bold;">#</td>
        <td style="font-weight: bold;">Date</td>
        <td style="font-weight: bold;">Opening Balance</td>
    </tr>
    @foreach($items as $groupitem)
    <tr>
        <td>{{ $loop->index + 1 }}</td>
        <td>{{ date('jS F, Y', strtotime($groupitem->first()->created_at)) }}</td>
        <td>
            @foreach($groupitem as $item)
               <span>{{ $item->name }}</span> - <span>{{ $item->opening_balance }} <br></span>
            @endforeach
        </td>
    </tr>
    @endforeach

</table>