How do i query to make total count per day based on a name or an id?
id | name |
---|---|
1 | |
2 | |
3 |
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',
]);