Get results group by date

Hi all,

I'm attempting to create a database call that returns a count of records by day for the past 5 days, but with the added wrinkle that days that have no records return a 0.

So in brief, if the table looked like this:

id      created_at
1       2018-04-08 06:00:00
2       2018-04-10 05:00:00
3       2018-04-10 06:00:00

I could get something like [1, 0, 2, 0, 0], since there's one record on the first day, none on the second, and two on the third.

Here's my eloquent query that returns, well, based on my example above, just [1,2].

Result::selectRaw('count(id) as total, date(created_at) as day')->where('user_id', $this->id)->where('created_at', '>=', Carbon::now()->subDays(5))->groupBy('day')->get();

Is there an easy way to get [1, 0, 2, 0, 0], even looping my result after the fact?

Thanks!

jlrdw

8 years ago

Level 75

Have you looked over any of these https://laracasts.com/discuss?q=group+by+date

brjohnson4

8 years ago

Level 5

I have, but my issues wasn't so much a "group by date" problem as it was a "return if null" problem. What I ended up doing is working with the result of the query, and looping through that to create a new array.

        $result = [];
        $day = 0;
        while ($day < 5) {
            $count = 0;
            foreach ($attemptsByDate as $attempt) {
                if($attempt->day == date_format(Carbon::now()->subDays($day), "Y-m-d")) {
                    $count = $attempt->total;
                }
            }
            array_push($result, $count);
            $day = $day + 1;
        }
        return $result;

It's probably not the fastest way to do this, but it works.

Please or to participate in this conversation.