CODEWITHSUNDEEP
mysqllaraveleloquentsubquery
Rashed Hasan
Rashed Hasan

Reputation: 3751

groupBy('created_at') using subquery in laravel eloquent

I am doing a report for inventory in my project. Now, I want to show items based on between date. I want to show the item list in the view by grouping the created_at something like bellow- enter image description here

My database table structure is - enter image description here

In the controller method, I wrote-

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'));
}

And in the blade, I am showing like -

<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>

Upvotes: 1

Views: 328

Answers (1)

rkj
rkj

Reputation: 8287

Use laravel groupBy after get method like this

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'));
}

In 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>

Upvotes: 1

Related Questions