CODEWITHSUNDEEP
phpmysqllaravelcounteloquent
owenmelbz
owenmelbz

Reputation: 6584

Laravel 5.3 distinct count, using eloquent instead of Query Builder

Come across a weird issue, where i originally tried

$usersWithAnswersCount = GameResult::where([
    'school_id' => null,
    'season_id' => $this->season->id
])
->groupBy('user_id')
->count();

My table looks like

enter image description here

However I was getting back the result "1" when it should have been returning back "2"

When I inspect what the query that ran was it was select count(*) as aggregate from game_results where (school_id is null and season_id = '1') group by user_id - which if i run directly as a mysql query returns back 2!!

So it looks like something within eloquent is changing my 2 into a 1 :(

However if i change this into the DB Query Builder and write out

$usersWithAnswersCount = DB::table('game_results')
->selectRaw('count(distinct user_id) as count')
->where([
    'school_id' => null,
    'season_id' => $this->season->id
])
->first()->count;

I get back "2" which is what I expected.

However I'm unclear why the Eloquent method fails, and what I can do to fix it if its possible.

Upvotes: 1

Views: 6876

Answers (2)

Saumya Rastogi
Saumya Rastogi

Reputation: 13709

You can use Laravel's distinct() method to get distinct records from the Database.

UPDATE

You can try this:

$usersWithAnswersCount = GameResult::where([
    'school_id' => null,
    'season_id' => $this->season->id
])
->distinct('user_id')
->count();

Hope this helps!

Upvotes: 1

anwerj
anwerj

Reputation: 2488

The query you making is not correct for use case, you can see the difference. 

select count(*) as aggregate from game_results 
where (school_id is null and season_id = '1') 
group by user_id order by user_id asc;

will return two rows

aggregate
1,
2

Eloquent picks first and return which is 1. 

select count(*) as aggregate from game_results 
where (school_id is null and season_id = '1') 
group by user_id order by user_id desc;

will return rows as

agrregate
2,
1

Eloquent will result 2 in this case.

What you want is count of (query) which will be again 2.

Getting It? what you want is DISTINCT

$usersWithAnswersCount = GameResult::where([
    'school_id' => null,
    'season_id' => $this->season->id 
])
->distinct('user_id')
->count();

Upvotes: 2

Related Questions