Querying data and grouping by day with Laravel

Question

I launch a website that stores images and users get a hot link.

I want to be able to query the records made over the past 7 days in the table containing the data of the uploaded image, extract only the created_at column and compile the data into an array, similar to creating a list of archives for a blog.

I would like the results to be presented as follows:

 [ 'Sunday' => 5, 'Monday' => 45, 'Tuesday' => 452, ... ] 

Where each number represents the number of entries created on each day. While I can output such an array, I can easily handle Javascript.

Anyone have any suggestions?

EDIT

This is the code I've tried so far:

 <?php class Admin { public function getCreatedAtAttribute($value) { $this->attributes['created_at'] = Carbon::createFromFormat('Ymd H:i:s', $value); } public static function uploadsGraph() { $date = \Carbon\Carbon::now(); $uploads = Upload::select('created_at')->where('created_at', '>=', \Carbon\Carbon::now()->subWeek())->get(); foreach($uploads as $date) { echo $date->created_at . '<br>'; } } } 

EDIT 2

Here is another version I tried, but that didn't work.

 class Admin { public static function uploadsGraph() { $date = \Carbon\Carbon::now(); $uploadsByDay = DB::table('uploads') ->select(DB::raw(' YEAR(created_at) year, MONTH(created_at) month, MONTHNAME(created_at) month_name ')) ->groupBy('year') ->groupBy('month') ->orderBy('year', 'desc') ->orderBy('month', 'desc') ->get(); dd($uploadsByDay); } } 

Answer 1

I assume that the number next to each day of the week represents the number of records made on that day, and the entire dataset that you want to query varies only in the last 7 days.

The idea here is to select the number of items created on the same day (completely ignoring part of the created_at column's created_at ), so we can use DB::raw inside the select() call to aggregate all the records created on a specific day, and then limit this data set to only those that were created last week. Something like this should work:

 $data = Upload::select([ // This aggregates the data and makes available a 'count' attribute DB::raw('count(id) as `count`'), // This throws away the timestamp portion of the date DB::raw('DATE(created_at) as day') // Group these records according to that day ])->groupBy('day') // And restrict these results to only those created in the last week ->where('created_at', '>=', Carbon\Carbon::now()->subWeeks(1)) ->get() ; $output = []; foreach($data as $entry) { $output[$entry->day] = $entry->count; } print_r($output); 

Also note that I suggested that this is a “rolling” week, where if today is Thursday, then the first date in the dataset will be the previous Thursday. It will not start last Sunday if that is what you need. If so, you can change the -where() condition to the following:

 ... ->where('created_at', '>=', Carbon\Carbon::parse('last sunday')) ... 

Answer 2

 DB::table("clicks") ->select("id" ,DB::raw("(COUNT(*)) as total_click")) ->orderBy('created_at') ->groupBy(DB::raw("MONTH(created_at)")) ->get();