如果Laravel中Count为零，如何获取记录 [英] How to get the record if Count is zero in Laravel

问题描述

我正在使用Chart.js显示每个月的预订数量.这是我的查询.一切正常. 我的问题是，如果一个月没有预订，那么它什么也不会显示.没有零值.在图表上，我得到的值就像

Im using Chart.js to show the number of bookings for each month. This is my query. It is working fine. My problem is if there is no booking in a month then it doesnt show anything. No zero value. On the chart i get the value like

• 1月23日
• 2月34日
• 4月23日
• 5月25日
• 7月42日

3月和6月显示在图表上..如何在没有预订的月份中获得零值.

March and June are shown on the chart..How can i get the value Zero for months where there is no booking..

$graph = DB::table('bookings')
    ->select(DB::raw('MONTHNAME(created_at) as month'), DB::raw("DATE_FORMAT(created_At,'%M %Y') as monthNum"),    DB::raw('count(*) as totalbook'))
    ->groupBy('monthNum')
    ->orderBy('created_at', 'asc')
    ->get();

要获得几个月的时间，

To get months,`

@foreach ($graph as $dat) 
                "{!! $dat->monthNum !!}",
                @endforeach`

并获取预订号码

@foreach ($graph as $dat) 
                {!! $dat->totalbook !!},
                @endforeach

推荐答案

MySQL不会填写不可用的月份，它只会根据可用的内容进行分组，然后为您提供这些结果.

MySQL will not fill in the months that aren't available, it will simply group by what you have available and then give you those results.

您可以做的是使用DatePeriod和Laravel的Collection类:

What you could do is use DatePeriod and Laravel's Collection class:

$results = DB::table('bookings')
    ->selectRaw("DATE_FORMAT(created_At,'%Y-%m-01') as monthNum, count(*) as totalbook")
    ->orderBy('monthNum')
    ->groupBy('monthNum')
    ->get();

$results = collect($results)->keyBy('monthNum')->map(function ($item) {
    $item->monthNum = \Carbon\Carbon::parse($item->monthNum);

    return $item;
});

$periods = new DatePeriod($results->min('monthNum'), \Carbon\CarbonInterval::month(), $results->max('monthNum')->addMonth());

$graph = array_map(function ($period) use ($results) {

    $month = $period->format('Y-m-d');

    return (object)[
        'monthNum'  => $period->format('M Y'),
        'totalbook' => $results->has($month) ? $results->get($month)->totalbook : 0,
    ];

}, iterator_to_array($periods));

请注意，我已更新查询中的monthNum，因为稍后会重新格式化.

Please note I have updated monthNum in the query as it will be reformatted later.

此外，您应该为此使用刀片原始标签({!! !!})，因为{{ $dat->totalbook }}应该可以正常工作.

Also, you should need to use the blade raw tags ({!! !!}) for this as {{ $dat->totalbook }} should work absolutely fine.

希望这会有所帮助！

这篇关于如果Laravel中Count为零，如何获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！