rendering SQL results into chart

[klukiyan]

Hi,
It's not really an issue with the package, it's rather with my insufficient skills.
Can somebody give me an example of rendering data from SQL results into chartjs array in the controller?
I cannot figure out how to put these 2 pieces together.
Thank you in advance

[fxcosta]

@klukiyan Hi! An example of query in Laravel

$articles = Article::all();

$articlesCommentCount = $articles->sum('comments'); // this returns a collection of total commands for every article. ps: this is a example only!

$chartjs = app()->chartjs
        ->name('lineChartTest')
        ->type('line')
        ->size(['width' => 400, 'height' => 200])
        ->labels(['January', 'February', 'March', 'April', 'May', 'June', 'July'])
        ->datasets([
            [
                "label" => "My First dataset",
                'backgroundColor' => "rgba(38, 185, 154, 0.31)",
                'borderColor' => "rgba(38, 185, 154, 0.7)",
                "pointBorderColor" => "rgba(38, 185, 154, 0.7)",
                "pointBackgroundColor" => "rgba(38, 185, 154, 0.7)",
                "pointHoverBackgroundColor" => "#fff",
                "pointHoverBorderColor" => "rgba(220,220,220,1)",
                'data' => $articlesCommentCount ,
            ],
        ])
        ->options([]);

return view('example', compact('chartjs'));

note the data attr in datasets method from chartjs. We pass the data structure that we created. Is it clearer now?

[klukiyan]

Hi @fxcosta ,
Thank you for your answer!
I wanted a multi-line chart where the number of Datasets varies depending on the data. I've somehow managed it, and it looks amazing. But left the question open, because I have a feeling that my approach is not the best.
So I have a $bsttop variable that is the result of my SQL query with 3 columns (SalesOrg, Month, CSP).
I've used this to get it to build me array with arrays that I then used in datasets.
Do you think it can be "rendered somehow better?

  // now let's try
        $kldataset = array(); // array for full dataset
        $kldatatemp = array(); //for details
        $thecolor = 0;
        $i=0; //for looping
        foreach($bsttop as $salesorg) {
            $thecurrent = $bsttop[$i]->sales_org;
            if($i == count($bsttop) -1) $thenext = 'xxxyyyzz'; else $thenext = $bsttop[$i+1]->sales_org;
            $kldatatemp[] = $bsttop[$i]->CSP;
            if($thecurrent !=  $thenext){
                    $r = rand(0,255); 
                    $g = rand(0,255); 
                    $b = rand(0,255);
                $kldataset[] = [
                    "label"=>$bsttop[$i]->sales_org,
                    "borderColor" => "rgba($r, $g,  $b , 0.7)",
                    "backgroundColor" => "rgba($r, $g,  $b , 0.2)",
                    "fill" => false,
                    "strokecolor" => "rgba($r, $g,  $b , 0.7)",
                    'data' => $kldatatemp,
                ];
                $kldatatemp = array();
            } 
            $i++;
       }
    // finally building up the chart
       $chartjs = app()->chartjs
        ->name('lineChartTest')
        ->type('line')
        ->size(['width' => 400, 'height' => 200])
        ->labels(['January', 'February', 'March', 'April', 'May', 'June', 'July','August','September','October','November','December'])
        ->datasets($kldataset)
        // ->text('Legend Position: right')
        ->options([]);

It works great, but I just think that there should be a better way to build the array than the newb-loop that I've made :)