Return Year Count Even When Count is 0

Posted 9 months ago by mprythero

I'm trying to work on a chart with a few datasets, however for the purposes of asking this question, I will only include one.

At the moment if I have the following in my controller:

$maintenancesForklifts = DB::table("equipment_attachments")
                        ->join('equipment as equip', 'equip.id', '=', 'equipment_attachments.unitID')
                        ->select(DB::raw('year(date) as year'), DB::raw("COUNT(*) as count"))
                        ->where('attachmentCategory','Maintenance')
                        ->where('equip.unit_type',3)
                        ->orderBy(DB::raw("year(date)"))
                        ->groupBy(DB::raw("year(date)"))
                        ->get();

I will get this returned:

[{"year":2005,"count":2},{"year":2006,"count":2},{"year":2010,"count":4},{"year":2011,"count":1},{"year":2012,"count":2},{"year":2013,"count":1},{"year":2014,"count":10},{"year":2015,"count":7},{"year":2016,"count":6},{"year":2017,"count":19},{"year":2018,"count":4}]

As you can see there are a few years missing if I went from say 2000 to the present day. Do you know how I could return the results so that they would come back with the years that have a zero count?

Thanks!

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.