Laravel consoletv/charts v 6.0 - Get count per day and per Item name

188 views Asked by At

How do i query to make total count per day based on a name or an id?

id name
1 Facebook
2 Twitter
3 Reddit
id page_id social_id visited_at
1 1 1 2021-03-27
2 1 1 2021-03-27
3 1 2 2021-03-27
4 1 2 2021-03-27
5 1 3 2021-03-27
6 1 3 2021-03-27
7 1 1 2021-03-28
8 1 1 2021-03-28
9 1 2 2021-03-28
10 1 2 2021-03-28
11 1 3 2021-03-28
12 1 3 2021-03-28

With the following query i get count of click on all social anchors per day, but i want to show in the chart also which social anchor has been clicked on that day.

$social_stats= Social::join('social_statistics', 'social_statistics.social_id','socials.id')
                   ->select( array(
                                   'social_statistics.visited_at as visited_at',
                                    DB::raw('count(*) as count'),
                                  )
                           )
                   ->orderBy('visited_at')
                   ->groupBy('visited_at')
                   ->pluck('count','visited_at')
                   ->all();

Need to render a Chart that shows by day the count of click on different social.

    $social_bar_chart = new SocialBarChart;


    $visited_at = collect(array_keys($this->social_bar));


    $social_bar_labels = $visited_at->map(function ($date) {
        return Carbon::parse($date)->format('d/m');
    })->toArray();



    $social_bar_chart->labels($social_bar_labels)
                      ->dataset('Social Count', 'bar', array_values($this->social_bar))
                      ->options([
                        'tooltip' =>['show' => true],
                        'backgroundColor' => '#54a0ff',
                    ]);
0

There are 0 answers