代码之家 › 专栏 › 技术社区 › Rashed Hasan Vijayaragavan

GroupBy(“created_at”)使用Laravel雄辩中的子查询

nested-queries subquery eloquent laravel mysql

1

Rashed Hasan Vijayaragavan · 技术社区 · 7 年前

我正在我的项目中做一个库存报告。现在,我想显示基于日期之间的 items 我想在视图中显示项目列表,方法是将创建的-


   
    
     我的数据库表结构是-
     
      
     
    
    
     在控制器方法中,我写道-
    
    public function inventorysubmit(请求$request)
{
$itemID=$request->项;
$startDate=carbon::分析($request->开始日期)->格式(“y-m-d”);
$endDate=carbon::parse($request->end_date)->格式('y-m-d');
$reportType=$request->报告类型;

如果($itemID){
$items=item::where('id',$itemid)->wherebetween('created_at',[$startdate,$enddate])->where('status',1)->get();
}否则{
$items=item::wherebetween('created_at',[$startdate,$enddate])->where('status',1)->get();
}
return view('report.inventory.searched result',compact('reporttype'、'startdate'、'enddate'、'items');
}
在刀刃上,我表现得像-

<table class=“Table Text Center”style=“text-Align:Left;width:100%;”>
<tr>
<td style=“font-weight:bold;”></td>
<td style=“font-weight:bold;”>日期</td>
<td style=“font-weight:bold;”>期初余额</td>
</tr>
@foreach($items作为$item)
<tr>
<td>$loop->索引+1</td>
<td>日期(“JS F,Y”,strtoTime($item->创建时间))</td>
<td>
<SPAN>$item->name</SPAN>-<SPAN>$item->期初余额<br></SPAN>
</td>
</tr>
@尾端foreach

</table>
created_at有点像贝罗-


我的数据库表结构是-


在控制器方法中,我写道-

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


在刀刃上,我表现得像-

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

1 回复 | 直到 7 年前

1

rkj 7 年前

groupBy get

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

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