I want to pass the my query output into jQuery chart js using Laravel 7

I want to show each month’s registered student count in the line chart. I got the result from the query but I can’t understand how to pass the value in the line chart js file. Please help to fix this issue. I attached the codes and the screenshots for my output.

I want to pass the query output data count value into jQuery data: [0, 4, 9, 18, 21, 34, 20, 35, 45, 53, 49, 60],

app.js

(function ($) {
  "use strict"; // Chart
  if ($('#report-line-chart').length) {
    var ctx = $('#report-line-chart')[0].getContext('2d');
    var myChart = new chart_js__WEBPACK_IMPORTED_MODULE_1___default.a(ctx, {
      type: 'line',
      data: {
        labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
        datasets: [{
          label: '# of total',
          data: [0, 4, 9, 18, 21, 34, 20, 35, 45, 53, 49, 60],
          borderWidth: 2,
          borderColor: '#3160D8',
          backgroundColor: 'transparent',
          pointBorderColor: 'transparent'
        }]
      },
      options: {
        legend: {
          display: false
        },
        scales: {
          xAxes: [{
            ticks: {
              fontSize: '12',
              fontColor: '#777777'
            },
            gridLines: {
              display: false
            }
          }],
          yAxes: [{
            ticks: {
              fontSize: '12',
              fontColor: '#777777',
              callback: function callback(value, index, values) {
                return value;
              }
            },
            gridLines: {
              color: '#D8D8D8',
              zeroLineColor: '#D8D8D8',
              borderDash: [2, 2],
              zeroLineBorderDash: [2, 2],
              drawBorder: false
            }
          }]
        }
      }
    });
  })($s);

My controller query

<?php
$arr["eachMonthRegister"] = DB::table('students')->select(DB::raw('count(id) as `data`'),  DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
        ->groupby('year','month')
        ->get();

        return view('home')->with($arr);
?>

My query output

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Actually You are doing it right, but i added some parts to your code.

– Controller

$result = DB::table('students')->select(DB::raw('count(id) as `data`'),  
DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
    ->groupby('year','month')
    ->orderby('month) // I added this line
    ->get();

$output = [];
    
foreach ($result as $entry) {
    if (isset($output[$entry->month]))
        $output[$entry->month] += $entry->data;
    else
        $output[$entry->month] = $entry->data;
}
$lineChartData= array_values($output);
return view('home')->with($lineChartData);

 // $lineChartData will be like below
 // [0, 4, 9, 18, 21, ...]

– home.blade

<script>
    var lineChartData = @json($lineChartData)
    // [0, 4, 9, 18, 21, ...]
</script>

– app.js

 .
 ..
 ...
 datasets: [{
      label: '# of total',
      data: lineChartData,

Another Note: it seems that you do not need year returned from DB. if it is so you can change codes like below and improve performance and speed of your code:

– Controller

$result = DB::table('students')->select(DB::raw('count(id) as `data`'),  
DB::raw(MONTH(created_at) month'))
    ->groupby('month')
    ->orderby('month)
    ->get();


// same as top codes
...

Method 2

I had similar issue JUST LIKE YOU have and solved yesterday! So, I am telling my full approach for you and anyone else facing this problem.

Problem 1:

Like you, I also thought about groupBy with year and month but after getting the output of the query, I figured out that it’s not groupBy which can solve our issue. Because, groupBy will only group/collect/count the values for the same month or year. So, we won’t get straight 12 values like in a year for each month (Specially for the month which has no value won’t give us 0 but we need 0 for our case). So, I had to create my own query like below. It will give you straight 12 values for each 12 month for the current year!:

Solution Query:

$months = ['01','02','03','04','05','06','07','08','09','10','11','12'];
$group_by_month = [];

// note that this foreach() will loop through exactly 12 times!
foreach($months as $key => $month){
    $group_by_month[$key] = DB::table('students')
                ->whereBetween('created_at',[date('Y').'-'.$month.'-01',date('Y').'-'.$month.'-31'])
                ->get()
                ->count();
}

$data['group_by_month_this_year'] = $group_by_month;

return view('home',$data); // in your home you can access $group_by_month_this_year

Problem 2:

The second problem I faced (probably you also will) is that the data returned was indexed array but we need array without index so json_encode will help, for @directive the short form is @json. So, add this line in your data:

data: @json($group_by_month_this_year)

Note: For my case, I needed the data for current and previous year, so I added the code of current year for you. If you want to do it from start to end then let me know, I will try to update the answer.

Method 3

You may implement line chart using the following ways.

I am going to use simple line chart from next URL to reduce code.
https://www.chartjs.org/docs/latest/charts/line.html

1. Laravel /routes/web.php and welcome.blade.php (template)

PHP will parse blade template file and convert it as HTML.

routes/web.php

...
Route::get('/', function () {
    $items = DB::table('students')
    ->select(DB::raw("count(id) as `data`, year || '-' || month as month"))
    ->groupBy('year','month')
    ->get();
    $values = $items->pluck('data'); // ["6","6","6","6","12"]
    $months = $items->pluck('month'); // ["2015-10","2015-11","2015-12","2016-1","2016-10"]  
    return view('welcome', ['values'=> $values, 'months' => $months]);
});

resources/views/welcome.blade.php

...
<head>
...
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
</head>
<body class="antialiased">
    <div>
        <canvas id="myChart"></canvas>
    </div>
...
    <script>
    var values = {!! json_encode($values) !!};
    var labels = {!! json_encode($months) !!};
    console.log("this is from welcome.blade.php",values,labels);
    const data = {
        labels: labels,
        datasets: [{
            label: 'My First dataset',
            backgroundColor: 'rgb(255, 99, 132)',
            borderColor: 'rgb(255, 99, 132)',
            data: values,
        }]
    };  
    const config = {
        type: 'line',
        data,
        options: {}
    };    

    var myChart = new Chart(
        document.getElementById('myChart'),
        config
    );    

    </script>   
</body>

php will parse this blade template and convert next code

var values = {!! json_encode($values) !!};
var labels = {!! json_encode($months) !!};

to

var values = ["6","6","6","6","12"];
var labels = ["2015-10","2015-11","2015-12","2016-1","2016-10"];

2. Laravel /routes/web.php and /public/js/custom.js ajax request

Browser will get HTML and then calling AJAX. After callback, chart js will be rendered with labels and values.

routes/web.php

...
Route::get('/', function () {
    return view('welcome');
});

Route::get('/ajax', function () {    
    $items = DB::table('students')
    ->select(DB::raw("count(id) as `data`, year || '-' || month as month"))
    ->groupBy('year','month')
    ->get();
    $values = $items->pluck('data'); // ["6","6","6","6","12"]
    $months = $items->pluck('month'); // ["2015-10","2015-11","2015-12","2016-1","2016-10"]
    return ['values'=> $values, 'months' => $months];
});

resources/views/welcome.blade.php

<head>
...
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>        
</head>
<body>
    <div>
        <canvas id="myChart"></canvas>
    </div>
...
    <script type="text/javascript" src="{{ URL::asset('js/custom.js') }}"></script>
</body>

public/js/custom.js

$.get( "/ajax", function( items ) {
    console.log("this data is from ajax request from custom.js",items);
    const data = {
        labels: items.months,
        datasets: [{
            label: 'My First dataset',
            backgroundColor: 'rgb(255, 99, 132)',
            borderColor: 'rgb(255, 99, 132)',
            data: items.values,
        }]
    };  
    const config = {
        type: 'line',
        data,
        options: {}
      };    

    var myChart = new Chart(
        document.getElementById('myChart'),
        config
      );    
});

Method 4

First, change your collection to array and only take the values of your collection

$array = $collection->toArray();
foreach($array as $a){
    $result[] = $a['data'];
}

return view('your.view', compact('result'));

And here’s my solution, avoid external JS file. Then pass your variable

here’s how :

<script>

...
datasets: [{
          label: '# of total',
          data: {!! json_encode($result) !!},
          borderWidth: 2,
          borderColor: '#3160D8',
          backgroundColor: 'transparent',
          pointBorderColor: 'transparent'
        }]
...
</script>

Method 5

You don’t pass the data well to the view. with() is used to flash data to the session.

Try like this in your Controller:

return view('home', ['mydata' => $arr]);

And you get it in the view with the variable $mydata

You can pass as many variable you want:

return view('home', ['mydata' => $arr, 'mydata2' => $arr2]);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0