Querying Data and Grouping by Day with Laravel

Question

I run a website that stores images and users get a hotlink.

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

I would like for the results to be presented like:

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

Where each number represents the number of records created on each day. As long as I can output an array like that, I can handle the Javascript side easily.

Anybody 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('Y-m-d 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's another version I tried, but that didn't work out well.

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'm assuming that the number next to each day of the week represents the number of records made on that day, with the entire dataset you want to query ranging only over the last 7 days.

The idea here is to select the count of items that were created on the same day (ignoring completely the timestamp portion of the created_at column), so we can use DB::raw inside of a select() call to aggregate all of the entries that were created on a specific day and then restrict that dataset to only those created in the last week. Something like this ought to 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 assumed this to be a 'rolling' week, where if today happens to be a Thursday, then the first date in the dataset will be the previous Thursday. It will not start on the most recent Sunday, if that is what you need. If it is, you can change the -where() condition to something like this:

...
->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();