laravel ->count() vs ->get()->count()

Question

Why are the two statement below behaving differentlY? The first returns 3 the second returns 1 for $progress. I thought that the first aggregates on the DB and the second on the server. So they should still return the same value.

$progress = $this->user->userActivities()->select('date')
                ->groupBy('date')
                ->get()->count();

$progress =  $this->user->userActivities()->select('date')
                ->groupBy('date')
                ->count();

Answer 1

->get()->count() will load Eloquent model objects into memory and then will count those.

->count() will use DB aggregate function, so it will definitely be more efficient:

select count(*) as aggregate ...

Answer 2

It's quite late answer but I had faced the same issue. These two examples are returning two different counts because of groupBy you have used.

$progress = $this->user->userActivities()->select('date')
            ->groupBy('date')
            ->get()->count();

This takes the rows first, then count the row numbers and returns the count.

$progress =  $this->user->userActivities()->select('date')
            ->groupBy('date')
            ->count();

Whereas this counts the rows by its group in the DB and returns the first group's row record count only from the list.

the solution I used instead of get()->count() for my case is like

$progress =  $this->user->userActivities()
            ->distinct('date')
            ->count('date');

Answer 3

Use ->get()->count() is wrong. I used ->get()->count() in my web app and when my database records have more than 80000 records I get error 500 in my app.