How to get the record if Count is zero in Laravel

Question

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

January 23
February 34
April 23
May 25
July 42

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`

And to get booking numbers

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

I'm not sure with Laravel, but generally speaking with SQL, you can add `UNION ALL 0` to display a value that would otherwise be omitted for being null: https://www.sqlservercentral.com/Forums/790871/return-a-row-with-constants-even-if-query-comes-up-blank#bm790946, although I would consider this more a hack than a solution — ctwheels, Jun 28 '17 at 16:42
im actually. DATE_FORMAT(created_At,'%M %Y') as monthNum.. i will update the question — stoneshaq, Jul 15 '17 at 14:07

Rwd · Accepted Answer · 2017-07-16T10:16:19.920

3

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.

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

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

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

Hope this helps!

edited Jul 16 '17 at 10:16

answered Jul 15 '17 at 15:46

Rwd

34,180
6
64
78

Call to a member function keyBy() on array i got this error.my laravel version is 5.1.46 (LTS) – stoneshaq Jul 16 '17 at 01:12
@stoneshak 5.1...for some reason I thought you were on 5.4. I'll update my answer with something more appropriate. :) – Rwd Jul 16 '17 at 07:10
thanks so much mate. works perfect except it doesnt show the data from the last month. change the datas in database to make sure but unfortunately it doesnt show the last month :( – stoneshaq Jul 16 '17 at 10:02
@stoneshak I missed that, good spot! I updated my post. Adding `->addMonth()` to `$results->max('monthNum')` should do the trick :) – Rwd Jul 16 '17 at 10:17
When there is no record i get this error.. Call to a member function addMonth() on null.. Is there a way to solve it? – stoneshaq Jul 30 '17 at 02:08
@stoneshak Use an `if` statement after the query to check you have results. – Rwd Jul 30 '17 at 06:55

score 0 · Answer 2 · answered Jun 28 '17 at 17:05

0

You have to set zero when there are no records for counting.

Change your query something like this:

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

Hope you understand.

answered Jun 28 '17 at 17:05

Sagar Gautam

9,049
6
53
84

1

Thanks for the effort. I put it exactly the same.. still works fine but unfortunately it doesn't give the zero value if there is no booking that month. – stoneshaq Jul 15 '17 at 07:12
@stoneshak I think, there is problem with `count(*)`, so please try with `count(id) `it will work. – Sagar Gautam Jul 15 '17 at 07:24

score 0 · Answer 3 · answered Mar 30 '21 at 13:08

0

In blade you can simply use

@foreach ($graph as $dat) 
    {{ $dat->totalbook ? $dat->totalbook : 0 }},
@endforeach

This checks if $dat->totalbook exists and if it does then displays the value, else will show 0.

answered Mar 30 '21 at 13:08

Tiago Martins Peres

14,289
18
86
145

score -1 · Answer 4 · edited Nov 02 '22 at 11:58

-1

Use eloquent

Model::withCount('members')
  ->having('members_count', '>', 0)
  ->get();

Use query builder doc

$users = DB::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();

edited Nov 02 '22 at 11:58

General Grievance

4,555
31
31
45

answered Aug 28 '22 at 20:02

ali hassan

321
2
5

How to get the record if Count is zero in Laravel

4 Answers4

Linked