Laravel 5.3不同的计数，使用雄辩而不是Query Builder [英] 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();

我的表看起来像

但是，当我收到结果1应该返回2

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

当我检查运行的查询是 select count（*）as game from game_results where（school_id is null and season_id ='1'）group by user_id - 如果我直接作为mysql查询运行返回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!!

所以看起来像雄辩中的东西是将我的2改为1：（

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

但是，如果我改变这个进入DB查询生成器并写出

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;

我收到2我的预期是什么。

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

但是我不清楚为什么Eloquent方法失败，如果可能，我可以做些什么来解决它。

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

推荐答案

您使用的查询对于用例来说是不正确的，您可以看到差异。

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;

将返回两行

aggregate
1,
2

雄辩的选择首先返回是1.

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;

将返回行为

agrregate
2,
1

雄辩将

你想要的是（查询）的数量，将再次是2。

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

DISTINCT

Getting It? what you want is DISTINCT

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

这篇关于Laravel 5.3不同的计数，使用雄辩而不是Query Builder的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！