GroupBy & Count records (Laravel 5.6, PostgreSQL 9.6)

I have a Spam Log table (Laravel 5.6, PostgreSQL 9.6):

+----+--------+---------------------+-------+
| id | isspam |      timestamp      | score |
+----+--------+---------------------+-------+
|  1 | t      | 2018-09-01 08:43:25 | 10.12 |
|  2 | f      | 2018-09-02 02:42:42 |  -0.5 |
|  3 | t      | 2018-09-03 00:39:39 |  8.71 |
+----+--------+---------------------+-------+

I'm trying to produce an array containing a count of spam = true grouped by day for the last 'x' number of days. e.g.

{
    "03": 1,
    "02: 0,
    "01": 1
}

I've tried:

$now = Carbon::now();  
$daily_spam_count = SpamLog::where('timestamp', '>=', $now->subDays(7))  
 ->where('isspam', true)  
 ->get()  
 ->groupBy(function($date){  
  return Carbon::parse($date->timestamp)->format('d');  
  });

But instead of the count, this gives me:

{
    "01": [
        {
        "id": 1,
        "isspam": true,
        "timestamp": "2018-09-01 08:43:25",
        "score": "10.12"
        }
    ],
    "02: [
        {
        "id": 2,
        "isspam": false,
        "timestamp": "2018-09-02 02:42:42",
        "score": "-0.52"
        }
    ],
    "03: [
        {
        "id": 3,
        "isspam": true,
        "timestamp": "2018-09-03 00:39:39",
        "score": "8.71"
        }
    ]
}

What am I missing in my query?

bobbybouwmann

7 years ago

Best Answer

Level 88

After the ->get() call you will get a collection back with all the SpamLog results. So you then group by the date format, but you never perform a count here.

You probably need something like this

$daily_spam_count = SpamLog::where('timestamp', '>=', $now->subDays(7))  
    ->where('isspam', true)  
    ->get() 
    ->groupBy(function($spamLog) {  
        return Carbon::parse($spamLog->timestamp)->format('d');  
    })->map(function ($spamLog) {
        return $spamLog->sum();
    });

You can probably also do it in one query

$dailySpamCount = SpamLog::select(DB::raw('DATE(timestamp) as date'), DB::raw('count(*) as count'))
    ->groupBy('date')
    ->get();

1 like

ElijahPaul

7 years ago

Level 1

@bobbybouwmann Thank you.

I understand now I wasn't counting the results.

Your first example works (with count() instead of sum())

.
.
.
    })->map(function ($spamLog) {
        return $spamLog->count();  
    });

With your second example I receive the following error:

SQLSTATE[42803]: Grouping error: 7 ERROR: column "spamlog.timestamp" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select DATE(timestamp) as date, count(*) as count from "spam... ^ (SQL: select DATE(timestamp) as date, count(*) as count from "spamlog" group by "date")

I'm wondering if it has anything to do with https://stackoverflow.com/questions/46909307/laravel-groupby-doesnt-work-properly-with-postgresql

Thanks again for your help.

bobbybouwmann

7 years ago

Level 88

@ElijahPaul Yeah count and group by are always an issue! Glad that I could help you out ;)

Please or to participate in this conversation.