CODEWITHSUNDEEP
javascriptphpmysqllaravelfusioncharts
draw134
draw134

Reputation: 1187

How to display all the days in chart for the current month using Laravel

Earlier I was working in how to display the data in the chart from the controller then to the front end itself. Now that I got it I need to display all the days in the current month in my graph since I can show the data in the graph because the current solution is not yet complete.

I used the created_at column in my table to base the date and I formatted it. You can show in query..

My query is like this

public function getGraph(){
    $ext = \DB::table('checkers')
        ->where('remarks_id',2)
        ->join('schedules','schedules.id','=','checkers.schedule_id')
        ->join('teachers','schedules.teacher_id','=','teachers.id')
        ->where('schedules.teacher_id',1)
        ->count();

    $date = \DB::table('checkers')
        ->where('remarks_id',2)
        ->select(\DB::raw("COUNT(checkers.id) `value` "), \DB::raw("DATE_FORMAT(checkers.created_at, '%M %d, %Y') label "))
        ->join('schedules','schedules.id','=','checkers.schedule_id')
        ->join('teachers','schedules.teacher_id','=','teachers.id')
        ->where('schedules.teacher_id',1)
        ->groupBy('label')
        ->get();        

    return response()->json([
        'count' => $ext,
        'date' => $date
    ]);
}

My json is 

{
  "count": 4,
  "date": [
    {
      "value": 1,
      "label": "January 21, 2020"
    },
    {
      "value": 3,
      "label": "January 23, 2020"
    }
  ]
}

My graph is like this enter image description here

There are blank spaces in the graph. What I want to do is fill the graph with the dates with the current month based on my query. Is this possible? I used fusioncharts and vue by the way.

Upvotes: 1

Views: 4051

Answers (1)

Hafez Divandari
Hafez Divandari

Reputation: 9029

Add day to selects of your query:

$date = \DB::table('checkers')
    ->select(
        \DB::raw("COUNT(checkers.id) `value`"),
        \DB::raw("DATE_FORMAT(checkers.created_at, '%M %d, %Y') label")
        \DB::raw("DATE_FORMAT(checkers.created_at, '%d') day")
    )
    //...
    ->get();

We need day to be able to merge.

Then get all days of current month using CarbonPeriod in your desired format and merge with the results of your query:

$daysOfMonth = collect(
    \Carbon\CarbonPeriod::create(
        now()->startOfMonth(),
        now()->endOfMonth()
    ))
    ->map(function ($date) {
        return [
            'value' => 0,
            'label' => $date->format('F d, Y'),
            'day' => $date->format('d')
        ];
    })
    ->keyBy('day')
    ->merge(
        $date->keyBy('day')
    )
    ->sortKeys()
    ->values();

Upvotes: 3

Related Questions