问题描述
过来一个奇怪的问题,我最初尝试过的
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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!